| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine 3 Macros
Evening,
I have three macros which i need to run off one button. The first two run fine but the third wont run. Can anyone tell me what I need to change to get them all to run simultaneously? Thanks LiAD Sub CreateComposition() 'AM to DJ Dim lngRow As Long For lngRow = 237 To 437 Call CopySpecialCells(lngRow, lngRow + 202) Next End Sub Sub CopySpecialCells(lngSourceRow As Long, lngTargetRow As Long) lngTemp = 39 For lngCol = 39 To 105 If IsNumeric(Cells(lngSourceRow, lngCol)) = False Then Cells(lngTargetRow, lngTemp) = Cells(lngSourceRow, lngCol) lngTemp = lngTemp + 1 End If Next End Sub Sub FillPlanningSheet() ' Columns("N:N").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5, Criteria1:="CB190" Columns("F:F").Select Selection.EntireColumn.Hidden = True Range("C48:O250").Select Selection.Copy Sheets("CB190").Select Range("D10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll ToRight:=10 Sheets("Base Planif.").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 Range("R48:R250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("N10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Base Planif.").Select Range("P48:P250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("U10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Sheets("Base Planif.").Select Application.CutCopyMode = False ActiveWindow.SmallScroll ToRight:=-6 Columns("M:O").Select Selection.EntireColumn.Hidden = False ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Columns("C:G").Select Selection.EntireColumn.Hidden = False Columns("D:D").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5 Range("A1").Select End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine 3 Macros
Have the first call the third:
Sub CreateComposition() 'AM to DJ Dim lngRow As Long For lngRow = 237 To 437 Call CopySpecialCells(lngRow, lngRow + 202) Next Call FillPlanningSheet End Sub -- Gary''s Student - gsnu200847 "LiAD" wrote: Evening, I have three macros which i need to run off one button. The first two run fine but the third wont run. Can anyone tell me what I need to change to get them all to run simultaneously? Thanks LiAD Sub CreateComposition() 'AM to DJ Dim lngRow As Long For lngRow = 237 To 437 Call CopySpecialCells(lngRow, lngRow + 202) Next End Sub Sub CopySpecialCells(lngSourceRow As Long, lngTargetRow As Long) lngTemp = 39 For lngCol = 39 To 105 If IsNumeric(Cells(lngSourceRow, lngCol)) = False Then Cells(lngTargetRow, lngTemp) = Cells(lngSourceRow, lngCol) lngTemp = lngTemp + 1 End If Next End Sub Sub FillPlanningSheet() ' Columns("N:N").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5, Criteria1:="CB190" Columns("F:F").Select Selection.EntireColumn.Hidden = True Range("C48:O250").Select Selection.Copy Sheets("CB190").Select Range("D10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll ToRight:=10 Sheets("Base Planif.").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 Range("R48:R250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("N10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Base Planif.").Select Range("P48:P250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("U10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Sheets("Base Planif.").Select Application.CutCopyMode = False ActiveWindow.SmallScroll ToRight:=-6 Columns("M:O").Select Selection.EntireColumn.Hidden = False ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Columns("C:G").Select Selection.EntireColumn.Hidden = False Columns("D:D").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5 Range("A1").Select End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine 3 Macros
A couple of comments about your code. These...
ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 can be replaced with this... ActiveWindow.ScrollColumn = 13 There is no reason to scroll one column (or sometimes 2 columns) at a time... just scroll to the column you want. Same goes for this... ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 You don't have to cound down one column at a time either; just specify the column you want to scroll to... ActiveWindow.ScrollColumn = 1 Also, you don't have to select cells or ranges in order to work with them as you did with this... Columns("N:N").Select Selection.EntireColumn.Hidden = True This would work as well... Columns("N:N").EntireColumn.Hidden = True Perhaps this previous posting of mine (a response to another person using Select/Selection type constructions) will be of some help to you in your future programming... Whenever you see code constructed like this... Range("A1").Select Selection.<whatever you can almost always do this instead... Range("A1").<whatever In your particular case, you have this... Range("C2:C8193").Select 'select cells to export For Each r In Selection.Rows which, using the above concept, can be reduced to this... For Each r In Range("C2:C8193").Rows Notice, all I have done is replace Selection with the range you Select(ed) in the previous statement and eliminate the process of doing any Select(ion)s. Stated another way, the Selection produced from Range(...).Select is a range and, of course, Range(...) is a range... and, in fact, they are the same range, so it doesn't matter which one you use. The added benefit of not selecting ranges first is your active cell does not change. -- Rick (MVP - Excel) "LiAD" wrote in message ... Evening, I have three macros which i need to run off one button. The first two run fine but the third wont run. Can anyone tell me what I need to change to get them all to run simultaneously? Thanks LiAD Sub CreateComposition() 'AM to DJ Dim lngRow As Long For lngRow = 237 To 437 Call CopySpecialCells(lngRow, lngRow + 202) Next End Sub Sub CopySpecialCells(lngSourceRow As Long, lngTargetRow As Long) lngTemp = 39 For lngCol = 39 To 105 If IsNumeric(Cells(lngSourceRow, lngCol)) = False Then Cells(lngTargetRow, lngTemp) = Cells(lngSourceRow, lngCol) lngTemp = lngTemp + 1 End If Next End Sub Sub FillPlanningSheet() ' Columns("N:N").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5, Criteria1:="CB190" Columns("F:F").Select Selection.EntireColumn.Hidden = True Range("C48:O250").Select Selection.Copy Sheets("CB190").Select Range("D10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll ToRight:=10 Sheets("Base Planif.").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 Range("R48:R250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("N10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Base Planif.").Select Range("P48:P250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("U10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Sheets("Base Planif.").Select Application.CutCopyMode = False ActiveWindow.SmallScroll ToRight:=-6 Columns("M:O").Select Selection.EntireColumn.Hidden = False ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Columns("C:G").Select Selection.EntireColumn.Hidden = False Columns("D:D").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5 Range("A1").Select End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine 3 Macros
Thanks a lot for the hints. Great to i can learn.
As I'm sure u guessed I'm a real VBA novice! Cheers "Rick Rothstein" wrote: A couple of comments about your code. These... ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 can be replaced with this... ActiveWindow.ScrollColumn = 13 There is no reason to scroll one column (or sometimes 2 columns) at a time... just scroll to the column you want. Same goes for this... ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 You don't have to cound down one column at a time either; just specify the column you want to scroll to... ActiveWindow.ScrollColumn = 1 Also, you don't have to select cells or ranges in order to work with them as you did with this... Columns("N:N").Select Selection.EntireColumn.Hidden = True This would work as well... Columns("N:N").EntireColumn.Hidden = True Perhaps this previous posting of mine (a response to another person using Select/Selection type constructions) will be of some help to you in your future programming... Whenever you see code constructed like this... Range("A1").Select Selection.<whatever you can almost always do this instead... Range("A1").<whatever In your particular case, you have this... Range("C2:C8193").Select 'select cells to export For Each r In Selection.Rows which, using the above concept, can be reduced to this... For Each r In Range("C2:C8193").Rows Notice, all I have done is replace Selection with the range you Select(ed) in the previous statement and eliminate the process of doing any Select(ion)s. Stated another way, the Selection produced from Range(...).Select is a range and, of course, Range(...) is a range... and, in fact, they are the same range, so it doesn't matter which one you use. The added benefit of not selecting ranges first is your active cell does not change. -- Rick (MVP - Excel) "LiAD" wrote in message ... Evening, I have three macros which i need to run off one button. The first two run fine but the third wont run. Can anyone tell me what I need to change to get them all to run simultaneously? Thanks LiAD Sub CreateComposition() 'AM to DJ Dim lngRow As Long For lngRow = 237 To 437 Call CopySpecialCells(lngRow, lngRow + 202) Next End Sub Sub CopySpecialCells(lngSourceRow As Long, lngTargetRow As Long) lngTemp = 39 For lngCol = 39 To 105 If IsNumeric(Cells(lngSourceRow, lngCol)) = False Then Cells(lngTargetRow, lngTemp) = Cells(lngSourceRow, lngCol) lngTemp = lngTemp + 1 End If Next End Sub Sub FillPlanningSheet() ' Columns("N:N").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5, Criteria1:="CB190" Columns("F:F").Select Selection.EntireColumn.Hidden = True Range("C48:O250").Select Selection.Copy Sheets("CB190").Select Range("D10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll ToRight:=10 Sheets("Base Planif.").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 Range("R48:R250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("N10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Base Planif.").Select Range("P48:P250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("U10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Sheets("Base Planif.").Select Application.CutCopyMode = False ActiveWindow.SmallScroll ToRight:=-6 Columns("M:O").Select Selection.EntireColumn.Hidden = False ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Columns("C:G").Select Selection.EntireColumn.Hidden = False Columns("D:D").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5 Range("A1").Select End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combine 3 Macros
Cheers
"Gary''s Student" wrote: Have the first call the third: Sub CreateComposition() 'AM to DJ Dim lngRow As Long For lngRow = 237 To 437 Call CopySpecialCells(lngRow, lngRow + 202) Next Call FillPlanningSheet End Sub -- Gary''s Student - gsnu200847 "LiAD" wrote: Evening, I have three macros which i need to run off one button. The first two run fine but the third wont run. Can anyone tell me what I need to change to get them all to run simultaneously? Thanks LiAD Sub CreateComposition() 'AM to DJ Dim lngRow As Long For lngRow = 237 To 437 Call CopySpecialCells(lngRow, lngRow + 202) Next End Sub Sub CopySpecialCells(lngSourceRow As Long, lngTargetRow As Long) lngTemp = 39 For lngCol = 39 To 105 If IsNumeric(Cells(lngSourceRow, lngCol)) = False Then Cells(lngTargetRow, lngTemp) = Cells(lngSourceRow, lngCol) lngTemp = lngTemp + 1 End If Next End Sub Sub FillPlanningSheet() ' Columns("N:N").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5, Criteria1:="CB190" Columns("F:F").Select Selection.EntireColumn.Hidden = True Range("C48:O250").Select Selection.Copy Sheets("CB190").Select Range("D10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.SmallScroll ToRight:=10 Sheets("Base Planif.").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 Range("R48:R250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("N10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Base Planif.").Select Range("P48:P250").Select Application.CutCopyMode = False Selection.Copy Sheets("CB190").Select Range("U10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Sheets("Base Planif.").Select Application.CutCopyMode = False ActiveWindow.SmallScroll ToRight:=-6 Columns("M:O").Select Selection.EntireColumn.Hidden = False ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Columns("C:G").Select Selection.EntireColumn.Hidden = False Columns("D:D").Select Selection.EntireColumn.Hidden = True Selection.AutoFilter Field:=5 Range("A1").Select End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combine two macros | Excel Discussion (Misc queries) | |||
| COMBINE TWO MACROS INTO ONE | Excel Programming | |||
| combine two macros | Excel Worksheet Functions | |||
| Combine 2 Macros | Excel Programming | |||
| Combine 2 macros | Excel Programming | |||
Linear Mode
