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.

 

[Joe4]

Was the workbook in question created in an earlier version of Excel? If so, it may be saved as an earlier version that might not have all the same functionality as your current version has, which would explain why it works in new workbooks, but not this one.

When you click "SaveAs", which Excel version does it give you as the default "Save As Type"? Is it your current version?

 

[IgorD]

Hi Tom!

Yo are right, except that it doesnt work And in the worst case (I mean if the name is not exactly "Instructions") it would just delete it, or break up telling it cant delete the last visible sheet, wouldn't it?

Mansy thanks,

Igor.

 

[IgorD]

Was the workbook in question created in an earlier version of Excel? If so, it may be saved as an earlier version that might not have all the same functionality as your current version has, which would explain why it works in new workbooks, but not this one.

When you click "SaveAs", which Excel version does it give you as the default "Save As Type"? Is it your current version?

Sorry to disappoint you - I did create it today from the scracth on the same software I am trying to run it right now. No changes whatsoever...

 

[Tom Urtis]

There needs to be at least one visible sheet in the workbook, meaning all sheets cannot be deleted.

At the top of the module, ABOVE and OUTSIDE the macro(s), enter the statement
Option Compare Text
and try the macro again.

Or, you have a hidden worksheet.

Try this:

Sub test()
Application.DisplayAlerts = False
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Instructions" And ws.Visible = xlSheetVisible Then ws.Delete
Next
Application.DisplayAlerts = True
End Sub

 

[IgorD]

Tom!

I did try to set 'Option Compare' - no difference;

For testing purposes I did unhide and unprotected all the sheets, so this should not be an issue. But I did try your suggestion with checking for visibilty - unfortunatly no effect.

Thanks,

Igor

 

[Tom Urtis]

This wil also check first to make sure the Instructions sheet exists.

Sub test2()
Dim x As Object, wsname$
wsname = "Instructions"
On Error Resume Next
Set x = ActiveWorkbook.Sheets(wsname)
If Err <> 0 Then
Err.Clear
MsgBox "No sheet exists named " & wsname
Exit Sub
End If
Application.DisplayAlerts = False
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Instructions" And ws.Visible = xlSheetVisible Then ws.Delete
Next
Application.DisplayAlerts = True
End Sub

 

[Tom Urtis]

I did try to set 'Option Compare' - no difference;

To clarify, I said Option Compare Text but in any case try the macro I just posted, it checks to make sure the Instructions sheet exists before attempting to delete everything except that sheet.

 

[IgorD]

Having found out, that it is always the same sheet which causes problems (not even its exact copies) I tried follwing:

1. In excel itself I can delete the sheet by right-clicking on the tab;
2. If I replace the code above at the right place with just

Worksheet("Start").Delete

I get the same message.

Sometimes I think my computer is just moody and does it on purpose!

 

[IgorD]

Tom,

I meant, of course, the same - I used "Option Compare Text".

I also ran your macro - it is surely better to do it that way, once it works. It didnt, so far.

Igor.

 

[Tom Urtis]

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