Results 1 to 6 of 6

trying to add more commands to my existing macro

Threaded View

  1. #1
    Forum Contributor
    Join Date
    04-26-2013
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    167

    trying to add more commands to my existing macro

    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
    Last edited by extremis; 05-01-2013 at 03:02 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1