Creating a Loop and pasting alternating values into an auto filter

[phisher12]

I am having an issue getting my value I copied to paste in to my autofilter. I have created a loop that will work down a list , copy the next value, past that value in an autofilter on a new sheet, copy the results found, and paste the section to the original sheet. The code will work down the list but I can't get the new copied values to carry over to the auto filter on the new sheet. Any ideas? Below is the code i have so far.</SPAN>

Sub Loop2()

Dim Order As Range
Dim LR As Long
Dim I As Integer

LR = Sheets("b").Range("A200000").End(xlUp).Row
Sheets("B").Select

For I = 1 To LR

Sheets("A").Select
Rows("1:1").Select
Selection.AutoFilter
Sheets("b").Select
Sheets("b").Range("C2:x752").ClearContents
Sheets("B").Select
Cells(I).Select
Selection.Copy

'The line below is where i get the error while trying to paste selection in criteria1
Sheets("A").Range("$A$1:$R$22").AutoFilter Field:=1, Criteria1:=Selection.Paste

Application.Goto Reference:="filterselection"
Range("A1:O77736").Select
Selection.Copy
Sheets("B").Select
Range("C2:X732").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("A").Select
Range("C2:Q2").ClearContents
Rows("1:1").Select
Selection.AutoFilter


Next I

End Sub

 

[skywriter]

I added a variable called filternumber and then assigned a value to it where you originally had the copy statement. I then replaced it in your criteria for auto filter. Just use Control F and use it to follow where I used it through the code.

So the long and short of what I found is you cannot copy something and then use a paste statement to use it as a criteria for an auto filter, it's an action, you have to provide a value. I hope this works for you.

Sub Loop2()

Dim Order As Range
Dim LR As Long
Dim I As Integer
Dim FilterNumber As Double


LR = Sheets("b").Range("A200000").End(xlUp).Row
Sheets("B").Select

For I = 1 To LR

Sheets("A").Select
Rows("1:1").Select
Selection.AutoFilter
Sheets("b").Select
Sheets("b").Range("C2:x752").ClearContents
Sheets("B").Select
Cells(I).Select
FilterNumber = Cells(I).Value

'Selection.Copy

'The line below is where i get the error while trying to paste selection in criteria1
Sheets("A").Select

Sheets("A").Range("$A$1:$R$22").AutoFilter Field:=1, Criteria1:=FilterNumber


Application.Goto Reference:="filterselection"
Range("A1:O77736").Select
Selection.Copy
Sheets("B").Select
Range("C2:X732").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("A").Select
Range("C2:Q2").ClearContents
Rows("1:1").Select
Selection.AutoFilter


Next I

End Sub

 

[StephenCrump]

I think the code below is closer to what you're looking for?

It assumes there is a range name for your filter data on Sheet A called DataRange (excluding the header row), and that the filter values you want are in Sheet B cells A1, A2, A3 etc.

The results for successive filters are pasted into the next available row in Sheet B, column B.

Sub Loop2()

    Dim LR As Long, lPasteRow As Long, i As Long
        
    LR = Sheets("B").Cells(Rows.Count, "A").End(xlUp).Row
    lPasteRow = 2   'Say
    Sheets("A").Rows(1).AutoFilter
    Application.ScreenUpdating = False
    
    For i = 1 To LR
        lPasteRow = Sheets("B").Cells(Rows.Count, "B").End(xlUp).Row + 1
        Sheets("A").Rows(1).AutoFilter Field:=1, Criteria1:=Sheets("B").Cells(i, 1)
        Range("DataRange").Copy
        Sheets("B").Cells(lPasteRow, 2).PasteSpecial Paste:=xlPasteValues
    Next i
    Sheets("A").Rows(1).AutoFilter Field:=1
    Application.ScreenUpdating = True
    
End Sub