Excel 2003 - VBA - Clear Multiple sheets

[Craig Brandt]

Hi:

I need to clear 31 sheets of data (daily downloads) and cycling through them
is timely and just not very elegent. (Look at me, trying to be elegent when
I can barely use VBA!)

What I did was turned on macro recording, selected several sheets then
depressed "Delete". After responding "Yes" to three popups asking if I
wanted to clear the QueryTables, it seemed to work. When I went back to look
at the generated Macro, it got hung up (Application-definded or
object-defined error) on the line deleteing the querytable, and this seemed
to happen before I even re-executed the macro.

Sheets("21").Select
Cells.Select
Sheets(Array("21", "22")).Select
Sheets("21").Activate
Selection.ClearContents
Selection.QueryTable.Delete

Any clues or alternate ways of accomplishing my intent?

Thanks,

Craig

 

[Bob Phillips]

Just try this

Worksheets(Array("Sheet21", "Sheet22", "Sheet23")).Select
Cells.ClearContents


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Craig Brandt]

Bob:

It cleared the first sheet, but not the other two.

Craig

btw: my sheets are actually named 21,22,23, etc. Hopefully, that's not going
to give me a problem.

 

[Roger Govier]

Hi Craig

Try

Worksheets(Array("Sheet21", "Sheet22", "Sheet23")).Select
Sheets("Sheet21").Activate
Cells.Select
Selection.ClearContents