excel 2010 vba help with AutoFilter.range.copy 2

[Martin King]

Hi, I'm looking for some help with the above. I have:


Set WSNew = Worksheets.Add(After:=Sheets(ActiveSheet.Index))

​

(*1) My_Range.AutoFilter Field:=5, Criteria1:="=ARBO"
With My_Range.Parent.AutoFilter.range

​

(*2) .Copy Destination:=WSNew.range("A1")

End With

Now the line at *1 applies the filter as expected, however when I call the line at *2 it pastes everything from the table not just the filtered results. Everything I can find when I search forums etc is that the autofilter.range.copy should copy only the visible rows unless more than 8192. My unfiltered table is 3500 rows. It's got me stumped.

Many thanks in advance.
Martin

 

[SkipVought]

Hi,

With My_Range.Parent.AutoFilter.Range.EntireRow.SpecialCells(xlCellTypeVisible)
'....

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

Even simpler:
[pre]With My_Range
.AutoFilter Field:=5, Criteria1:="=ARBO"
.Copy Destination:=WSNew.range("A1")[/pre]

combo

 

[Martin King]

Brilliant, Thank you to you both. Both methods work exactly as I wanted.

As I'm sure its the same for everybody who posts here, hours I spent for something that you two have fixed in seconds. A great Post raised for you both.