trying to add more commands to my existing macro

**extremis** · 05-01-2013, 10:13 AM

Hi,

I put the instruction in the excel file, what I would like to do. I try to add command into my VBA but it doesnt work. I put in bold the command that I added. The first command to filter macro work. The second one as well but as a DIFFERENT macro. I would like to only have ONE macro.

Sub test()
'
' test Macro
'

With sheet ("045")

with 
Dim lLR As Long         ' last row of data
Dim lLC As Long         ' last column of data
Dim rDataRange As Range ' data range

' determine last row of data based on column A
lLR = Cells(Rows.Count, 1).End(xlUp).Row
' determine last column of data based on row 1
lLC = Cells(1, Columns.Count).End(xlToLeft).Column
' use lLR and lLC to define data range
Set rDataRange = Range(Cells(2, 22), Cells(lLR, lLC))

' filter the defined range
rDataRange.AutoFilter _
    Field:=21, _
    Criteria1:="=PP*", _
    Operator:=xlAnd

' filter the defined range
rDataRange.AutoFilter _
    Field:=22, _
    Criteria1:="=0451*", _
    Operator:=xlAnd
    
' filter the defined range
rDataRange.AutoFilter _
    Field:=23, _
    Criteria1:="=3", _
    Operator:=xlAnd


' copy the visible cells in the defined range
rDataRange.SpecialCells(xlCellTypeVisible).Copy _
    Sheets("double eliminator").Range("m2")
' to Range("m2") on Sheets("double eliminator")
end with

end with sheet ("Double eliminator")

with Range("M2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-33
    ActiveWindow.ScrollRow = 370
    ActiveWindow.ScrollRow = 363
    ActiveWindow.ScrollRow = 342
    ActiveWindow.ScrollRow = 246
    ActiveWindow.ScrollRow = 156
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 1
    Range("A2").Select
    ActiveSheet.Paste
    Range("I6").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Range("L6").Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("SUMMARY").Select
    Range("H12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Double Eliminator").Select
    Range("K13").Select
    Application.CutCopyMode = False

test1.xlsm

**pmizzel** · 05-01-2013, 10:35 AM

Although you could try to build a million macros into one, I would suggest writing seperate macros and then calling them all at the same time. It will help keep your code organized and making changes will be much easier.

Sub FINALMACRO()
Application.ScreenUpdating = False

    Call MacroName1
    Call MacroName2
    Call MacroName3

End Sub

The code above runs in order (1, 2, 3, ect...).
Hope it helps!

**extremis** · 05-01-2013, 11:27 AM

Macroname1 is on sheet 2.
Macroname2 is on sheet 3.

the finalmacro button is on sheet1.

help?

**pmizzel** · 05-01-2013, 11:33 AM

Try putting them all in 'ThisWorkbook' if you can...

It would look somthin like this in 'ThisWorkbook'


Sub FINALMACRO()
Application.ScreenUpdating = False

    Call Macro1
    Call Macro2

End Sub

Sub Macro1()

'Your Code....

End Sub

Sub Macro2()

'Your Code....

End Sub