Cant delete all (except one) sheets

[IgorD]

Hello Forum!

I would like to delete all except one sheets in the workbook by pressing onto a button. So I wrote the folllowing code (its obviously only a part)

Application.DisplayAlerts = False
For Each Worksheet In Worksheets
If (Worksheet.Name <> "Instructions") Then
Worksheet.Delete
End If
Next
Application.DisplayAlerts = True

and put it into a Module in the same workbook. The button itself is in one of the sheets to be deleted, so I assign a simple macro to it which is just calling the code above.

Well, sounds simple, but it does not work, with "Subscript out of range" error at the attempt to delete one (not the first one) of the sheets.

Searching around suggested that the problem appears when the worksheet I try to access does not exist (missspelling of a name or so). but in my code I dont really use it.

I am out of ideas! Any help is highly appreciated!

Thanks in advance!

Igor.

 

[IgorD]

Tom,

Make sure you are out of error debug mode, did you try my test2 macro.

To be sure I reopend the Excel and the Workbook. Your macro test2 (the one checking for the existence of the worksheet is working fine, until it gets to the "Start" sheet - then the same behaviour as before..

Igor.

 

[Tom Urtis]

Well then there is some conflicting code or properties that you are unaware of or are not saying. Maybe the Start worksheet module or the Workbook module has some procedure in it that causes an error when the sheet is deactivated or deleted. For that Start sheet, right click on its tab, left click on View Code, and see what code, if any, is there. Something in the workbook is going on outside the realm of these macros.

 

[IgorD]

Guys,

thanks a lot for help to all of you!

After having a sleepless night and having serious doubts about my mental health I found out, that Excel does not care much to show the correct code line while having this error message.

Basically, the error appeared on on the Worksheet.Delete line, but the actual mistake was some 10 lines later. And this happened even in the debuger step-by-step mode. And te mistake was classical - typos.

Sorry for stealing your valuable time! Be aware!

Igor.