Copy Paste of Filtered Data "Uses" too many Cells

[Guest]

Using XL 2003 & 97

Have about 8000 rows of data in 18 Columns.

Using Autofilter, I filtered the data down to about 5000 cells

After inserting a new sheet, and using VBA, I copy/pasted the filtered data
to the New Worksheet.

Then I noticed that the New WS had a used range of >65,000 rows by 18 columns.

Of course this is not efficient.

What is causing the excess cell "Used Range"?

The code that I am using to copy paste follows:
.........
Sheets.Add.Name = "New"
Sheets("Filtered Data").Activate
Cells.Copy
Sheets("New").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

TIA

Dennis

 

[Ron de Bruin]

Hi Dennis

You copy all the cells to the new sheet
Cells.Copy

See the example from Tom on debra's site
http://www.contextures.com/xlautofilter03.html#Copy