do you have issues?
For me it works fine and with the cisible cells you are on the save
side.
But Copy method only copies visible cells and so SpecialCells are not
needed:
Sub Many_To_One_Copy_1()

Dim i As Long
Dim MyArr As Variant
Dim lr As Long
Dim rngE As Range, c As Range

MyArr = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")

Application.ScreenUpdating = False

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

With Sheets(MyArr(i))
.AutoFilterMode = False

lr = .Cells(.Rows.Count, 5).End(xlUp).Row
Set rngE = .Range("$D$1:$E$" & lr)

rngE.AutoFilter Field:=1, Criteria1:="=X"
.Range("$E$2:$E$" & lr).Copy
Sheets("Sheet1").Range("AG" & Rows.Count).End(xlUp)(2).PasteSpecial
Paste:=xlPasteValues

.AutoFilterMode = False

End With
Next 'i

Application.ScreenUpdating = True
End Sub


Regards
Claus B.


Hi Claus,

No issues to speak of, was wondering if I was using VisibleCells correctly, or even if I should omit VisibleCells since I only wanted the E column.

One method I used VisiblCells and it copied Headers, column D and column E of filters data.

Thanks.

Howard