PDA

View Full Version : running some macros automatically



mary.m.t
01-24-2012, 09:36 AM
I have attached extra rudimentary of my attempt to solve what I need:banghead::banghead:.
I want to do the following things with this excel:

1.macroul copydateclient, to copy data (F2: l2) from sheet master if e2 meets one of three conditions: E2 = aa or bb or cc, in Sheet kk at b2:h2, and after that the data will not be possible to change , otherwise the cells may be changed(b2;h2)

2.macroul copynrinreg, will send me to another sheet and copy nr.inregistrare, I have 8 sheet and the macro will take me to one of this sheets if o2 in the sheet master is O2 = DD or EE or FF or GG or HH or ii or jj or kk


3.and this 2 macros to run one after anothers automatically


I know it's a little abstract but I can’t give actual data ... but I can assure you that this is what I want to realize with this excel.

I really need your help: pray2:: pray2:: pray2::help! Tanks very much! Mary

mdmackillop
01-24-2012, 02:18 PM
Welcome to VBAX

Here's a shortened version of your code (if I understand your requirements correctly).
There is an issue with the Offset(,-12) address as this does not exist if the ActiveCell is Column E

Sub CopyDateClient()

Application.ScreenUpdating = False

Dim cel As Range
Dim tgt As Range
Set cel = ActiveCell

Select Case cel.Value

Case "aa", "bb", "cc", "fost_client"
Set tgt = Sheets("kk").Range("B65536").End(xlUp).Offset(1, 0)
tgt.Resize(, 7).Value = cel.Offset(, 1).Resize(, 7).Value
Application.Goto tgt.Offset(1, 7)



Case "dd", "ee", "ff", "gg", "hh", "ii", "jj"
sh = cel.Value
Sheets(sh).Range("A65536").End(xlUp).Offset(1, 0).Value = cel.Offset(0, -12).Value
Application.Goto Sheets(sh).Range("A65536").End(xlUp).Offset(0, 1)

Case Else
sh = Left(cel.Value, 2)
Sheets(sh).Range("A65536").End(xlUp).Offset(1, 0).Value = cel.Offset(0, -12).Value
Application.Goto Sheets(sh).Range("A65536").End(xlUp).Offset(0, 1)

End Select
Application.ScreenUpdating = True
End Sub

mary.m.t
01-24-2012, 02:46 PM
but it does't work!

mdmackillop
01-24-2012, 03:09 PM
A little more information would assist.
Your dropdowns don't match the sheet names, there is no offset as you coded as stated above. Give us a better example.

mary.m.t
01-24-2012, 03:30 PM
Sub CopyDateClient()
Dim r As Range, lRow As Long, iCol As Integer

Application.ScreenUpdating = False

With Sheets("Master")
For Each r In .Range(.Cells(2, "E"), .Cells(2, "E").End(xlDown))
With Sheets("kk")

With .UsedRange.CurrentRegion
lRow = .Row + .Rows.Count
End With

Select Case r.Value
Case "aa", "bb", "cc"
For iCol = 2 To 8
.Cells(lRow, iCol).Value = r.Offset(0, iCol - 1).Value
Next
End Select
End With
Next
End With
Application.ScreenUpdating = True
End Sub

i want to change this code to copy cell c, f, g, h, i, j, k, l form sheet master in sheet kk.

and then to change this code:
Sub CopyNrInreg()
Dim r As Range, lRow As Long '
Dim sSheet As String '

Application.ScreenUpdating = False

With Sheets("Master")
For Each r In .Range(.Cells(2, "O"), .Cells(2, "O").End(xlDown)) '
sSheet = Left(r.Value, 2)
Select Case sSheet
Case "dd", "ee", "ff", "gg", "hh", "ii", "jj", "kk" '
With Sheets(sSheet)

With .UsedRange.CurrentRegion
lRow = .Row + .Rows.Count
End With

.Cells(lRow, "A").Value = r.Offset(0, iCol - 12).Value
End With '
End Select '
Next
End With
Application.ScreenUpdating = True
End Sub

By if i select kk in cell o of sheet master go to sheet kk and run macro copydateclient, if i select jj in cell o of sheet master just go to sheet jj.

mary.m.t
01-24-2012, 03:37 PM
p.s and copy cell c from sheet master in sheet jj at cell a
in sheet kk c will be copy in cell a, f in b etc

mdmackillop
01-25-2012, 03:39 PM
Sorry, I don't understand what your references are.

mary.m.t
01-26-2012, 02:26 AM
Thanks i've fixed it!