View Full Version : Copy all sheets (filter some rows)

06-15-2005, 10:48 PM
Dear all,

I wanna copy all worksheets within the workbook to new ones...

But I want to skip all rows with a value "X" or empty cell in Column A..before
copying to new sheets..

The name of new sheets will be the header in col A.

For example, in sheet 1, A1 is May, then a new sheet "May" is created copying all rows in sheet1 (without empty cell/X) in col A..

For example, in sheet 2, A1 is June, then a new sheet "June" is created copying all rows in sheet2 (without empty cell/X) in col A..

Can anyone give me clue?

Thank you. :help

Ken Puls
06-15-2005, 11:03 PM
Hi Elita,

For the filtering aspect of this, I would record the steps for Autofilter to select all rows that do not equal X, and then copy those to your new sheet.

Just a quick tip with autofilter, though... I prefer to set the AutofilterMode property on the sheet to false before I start, then set the autofilter row, then trigger the filter. Copy the items, then set the autofiltermode to false again. This will ensure that you always get the data without errors or missing data.

This is totally untested, but...

On error resume next
Activesheet.autofiltermode = false
on error goto 0
'Set you autofilter on row 1 here

Hope it helps!

06-16-2005, 08:41 AM
Hm... I think I can get what you mean...

I'll try to work on it, thanks Ken! :rotlaugh: