View Full Version : [SOLVED:] Copy and paste from a filtered list...
Oorik
12-18-2004, 08:53 AM
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
12-18-2004, 10:39 AM
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
12-21-2004, 04:48 PM
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
01-08-2005, 07:44 AM
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
03-05-2005, 08:13 AM
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.