Copy and paste from a filtered list...

[Oorik]

I want to use autofilter and copy/paste the results of the
filtering but all the data in the hidden rows is copied too.

I used to know how to do it in excel97 but I seem to have
forgotten.

Now I'm using officeXP and my memory fails me.

Thanks for any assist. :dunno

Oorik

[Zack Barresse]

Hi, welcome to the board!!

Use SpecialCells to copy the Visible Cells. Not sure if the syntax is 97 compliant (as I don't have it to test on this machine), but maybe ...



Option Explicit

Sub TestMeFoo()
Dim Filter_Range As Range
'** Set our range to filter, recommended
Set Filter_Range = Sheets("Sheet1").Range("A1:I" & Sheets("Sheet1").Range("I65536").End(xlUp).Row)
'** Copy the visible filtered data
Filter_Range.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
'** Turn off Autofilter, optional, if desired
Sheets("Sheet1").Cells.AutoFilter
'** Turn off Copy mode, w/ the magical dancing ants
Application.CutCopyMode = False
End Sub

[Ken Wright]

Albeit not 100% in line with your question, another option is to consider using Data / Filter / Advanced Filter and the 'Copy to another location' option.

[Oorik]

Both of your suggestions will work but the copy
to destination won't in this application.

The code snippet is what I needed...

Thanks again, works great...:vv

Oorik

[Oorik]

I don't have to use filters/copy/paste at all.

Reduced the time of the routine from 12 to 2... WOW...

Basically took a 110x12 range, cut it into 8 diff categories
and printed each, by category, on a diff sheet.

Used the fact that arrays can be copied from and loaded
into ranges almost instantaneously.

Whew...