Dynamic Macro to copy rows based on selected criteria

[Mavericks334]

Hi

Below is my macro to copy rows based on selected criteria. The Row number would be changing every month.

Sheets("Active Vendors").Select
Range("A1").Select
Selection.AutoFilter
Range("AD1").AutoFilter Field:=34, Criteria1:=Array( _
"KPMG Advisory SRL", "Magna Infotech Pvt Ltd", "PRICEWATERHOUSE COOPERS LLP", _
"S R Batliboi"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$AP$26").AutoFilter Field:=34, Criteria1:=Array( _
"KPMG Advisory SRL", "Magna Infotech Pvt Ltd", "S R Batliboi"), Operator:= _
xlFilterValues
Range("AF5:AL5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

The row may not always start at A5, could be at a2 or even a6.

How do i make my macro dynamic.

 

[AlphaFrog]

Try this. It will copy only the visible filtered rows even though it starts at row 2.

    [color=darkblue]With[/color] Sheets("Active Vendors")
        
        .Range("A1").AutoFilter
                    
        .Range("A1:AP26").AutoFilter Field:=34, Criteria1:=Array( _
              "KPMG Advisory SRL", "Magna Infotech Pvt Ltd", _
              "S R Batliboi"), Operator:=xlFilterValues
              
        [color=green]'Copy visible rows from AF:AP[/color]
        .Range("AF2", .Range("AF" & Rows.Count).End(xlUp)).Resize(, 7).Copy
        
    [color=darkblue]End[/color] [color=darkblue]With[/color]

 

[Mavericks334]

The PRoblem is i dont wish it copies only from AF2. It needs to copy from A2. and the rows will not be limited to 26. could even be more or less.

 

[Mavericks334]

Windows(Fname).Activate
Sheets("Active Temp Agency_2").Select
Selection.AutoFilter
Range("AD1").AutoFilter Field:=30, Criteria1:= _
"Investment Management Services"
Range("A1:AL1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("FTS_HC.xls").Activate
Sheets("Active Temp Agency").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Active Vendors").Select
Range("A1").Select
Selection.AutoFilter
Range("AD1").AutoFilter Field:=34, Criteria1:=Array( _
"KPMG Advisory SRL", "Magna Infotech Pvt Ltd", _
"S R Batliboi"), Operator:=xlFilterValues
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Active Temp Agency").Select
Selection.End(xlDown).Select

 

[Mavericks334]

I used Rows("2:2"), so it would take me there once i do the autofilter and it works great