Looping through sheets to clear a specified range, but excluding some sheets

Hi there,

I am trying to run a small macro to clear a range on all worksheets except sheets 1-7.

I find it is clearing the ranges as needed, but is including sheets 2-7 as well. It is only excluding Sheet1 from the procedure.

Anybody have some insight as to why?

Here is the code I am using:

Please Login or Register  to view this content.

Many thanks in advance.

The UCase function is making all upper case the sheet name to test, but none of the sheet names to exclude are upper case.
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7"
I'm surprised it is excluding one sheet. Perhaps you should remove the UCase function.

Also, you can replace this...
Sheets(ws.CodeName).Range("A2:AA500").Clear

with this...
ws.Range("A2:AA500").Clear

Do you think it has something to do with the fact that you're specifically selecting the UPPERcase ws.codename, but your Case statement refers to the Sheets by Sentence Case.

To clarify, if you're selecting the UPPERcase name, shouldn't your CASE statement be Case "SHEET1", "SHEET2", "SHEET3" etc.?

Play with that idea for a bit and see if that helps.

Edit: Smile... got beat again, but just a couple of minutes.

Yes - that helped. Thank you both.

Here is the code that is working correctly in case somebody wants to reference it in the future:

Please Login or Register  to view this content.