Deleting Sheets Macro

[Excel Newbie05]

Hi Everyone,

I wrote the function below so it could
1) Clear all contents in sheet 1 range C9:AI50,AK9:AN50
2) Rename worksheet 2 to "TEMPLATE" and cell A1 in worksheet 2 to "TEMPLATE"
3) Delete all other worksheets in the workbook but sheet 1 and 2.

Now Here is my issue, this code works, expect for when I have only 2 worksheets?? Can someone give me some insight on what I may be doing wrong??

Thanks!

Please Login or Register  to view this content.

[VBA Noob]

Add a if statement

e,g

Please Login or Register  to view this content.

VBA Noob

[Excel Newbie05]

Hi VBA Noob,

I tried that but doesn't seem to work...

If I add that If Statement then it works properly when their are two sheets, but not when there are more than two...

I am not sure why?

Thanks again!

[VBA Noob]

Try

Please Login or Register  to view this content.

VBA Noob

[dominicb]

Good evening Excel Newbie05

It seems to be because of the line :

Please Login or Register  to view this content.

If Worksheets.Count is less than three, then it cannot count backwards unless you qualify the statement with a Step -1 instruction. The loop will do nothing.

As you variables (j in particular) are quantified within the loop, they are set to 0 outside the loop, Excel is not picking up and deleting the sheets it should.

HTH

DominicB

[VBA Noob]

To turn off the sheet delete message add this at the start

Please Login or Register  to view this content.

and this at the end

Please Login or Register  to view this content.

VBA Noob

[Excel Newbie05]

Thank you VBA Noob! This worked Wonders!!