Macro help

[John Davis]

Hello All:

I have the following:

Sub Test()
Dim x As Variant
Dim i As Long
Dim lr As Long

lr = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1

With Sheets("Sheet3")

x = Array("WORD1", "WORD2", "WORD3", "WORD4")

For i = LBound(x) To UBound(x)

    .Range("A2:AA" & lr).AutoFilter Field:=6, Criteria1:=x
    .Range("A2:AA" & lr).EntireRow.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet4").Range("A" & lr2)
    .Range("A2:AA" & lr).AutoFilter

Next i

End With

End Sub

Can someone tell me why it does not work??

 

[Andrew Poulsom]

Do you mean it's always [asting in the same row? You need to repeat this within your loop:

lr2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[Trevor G]

You haven't defined Lr2

lr2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[John Davis]

Do you mean it's always [asting in the same row? You need to repeat this within your loop:

lr2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1

Thanks for your reply Andy. I took your advice. But still having problems trying to get it too advance through the Array? The goal is to continue filtering copying and pasting too Sheet4 for each word in the Array. As is it only does the first word.

 

[John Davis]

You haven't defined Lr2

lr2 = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Row + 1

OK. Thanks for input Trevor. I defined it, but it is still not working?? It autofilters/copies once and then stops.

 

[Andrew Poulsom]

It's:

.Range("A2:AA" & lr).AutoFilter Field:=6, Criteria1:=x(i)

 

[John Davis]

It's:

.Range("A2:AA" & lr).AutoFilter Field:=6, Criteria1:=x(i)

Wow. Thanks Andy. I tried it with x, I tried it with i. Couldn't figure it out. Thanks for the help.