Excel Macro to filter/paste data

[Tuxracer]

Hello, I have searched and found examples closely related to what I am trying to do, but still need help. I have recorded a macro of the desired process, shown below.

AutoFilter Field:=1, Criteria1: will vary based on what the user populates in cell Sheet1!B1
AutoFilter Field:=1, Criteria1: will vary based on what the user populates in cell Sheet1!D2

I need to filter Sheet2 based on what the user specifies in those two cells on Sheet1, copy the filtered data from Sheet2 and then paste special values back into Sheet1

Sheet2 has approximately 10,000 rows of data to be filtered.

Sub Macro2()
'
' Macro2 Macro
'


'
    Sheets("Sheet2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$AD$1:$AO$10222").AutoFilter Field:=1, Criteria1:="123"
    ActiveSheet.Range("$AD$1:$AO$10222").AutoFilter Field:=2, Criteria1:="456"
    Range("AF17:AH22").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A12").Select
End Sub

 

[Tuxracer]

Sorry one edit to the above post.

AutoFilter Field:=1, Criteria1: will vary based on what the user populates in cell Sheet1!B1
AutoFilter Field:=2, Criteria1: will vary based on what the user populates in cell Sheet1!D2

 

[Fluff]

Hi & welcome to the board
Try

    ActiveSheet.Range("$AD$1:$AO$10222").AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Range("B1").value
    ActiveSheet.Range("$AD$1:$AO$10222").AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("D2").value

 

[Tuxracer]

Hi, thank you for the quick reply and the welcome

I tried replacing my two AutoFilter lines with your code. How can I copy the resulting filtered rows? I still have my old range defined

Sorry I'm pretty new to macros and vba but trying to learn

Range("AF17:AH22").Select

 

[Fluff]

This assumes that your data starts in row 2 & that you don't want to copy the header row

Sub Macro2()
'
' Macro2 Macro
'


'
    Sheets("Sheet2").Select
    Selection.AutoFilter
    ActiveSheet.Range("$AD$1:$AO$10222").AutoFilter Field:=1, Criteria1:=Sheets("sheet1").Range("B1")
    ActiveSheet.Range("$AD$1:$AO$10222").AutoFilter Field:=2, Criteria1:=Sheets("sheet1").Range("D2")
    Range("AF2:AH10222").SpecialCells(xlVisible).Copy
    Sheets("Sheet1").Select
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A12").Select
End Sub

 

[Tuxracer]

This worked perfectly. Thank you very much!!

 

[Fluff]

Glad to help & thanks for the feedback