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.

 

[TheNoocH]

welcome to the board...

how about just putting the button on the Instructions sheet?
or possibly assign it a shortcut key instead of a button?

 

[Joe4]

Welcome to the Board!

I tested out your code, and it appears that it works fine.

If you hit the DEBUG key when you get the error, which line of code does it highlight?

Are you putting the code in a Standard Module, or one of the Worksheet Modules (like one that you are deleting)?

I would try separating this code to its own Sub Procedure and try running it by itself to see if the problem really is with this section of code, or intereaction with your other code.

 

[IgorD]

Hi!

Thanks for immediate reply!

Having a short cut is not good, because users will have always to remember it - and you probably know how users are.

Putting the button onto "Instructions" itself is not an option either, since it is almost always hidden. BUT I do unhide it before I start to delete.

Regards,

Igor.

 

[IgorD]

Thanks Joe4!

The code is so simple it should work fine...

When I am in debug mode, I can see sheets being delted, but at some point it tells me: "Cant enter break mode at this time" and then after pressing continue I get the "Subscript out of range" message. It stucks directly on Worksheet.Delete line.

Not sure what "Standard Module" is, but the code is placed in the "Modules" part in Module1

I have tried already separating the code into separate Sub, it works on simple sheets, but not on the one where iot should. But I cant think of anything, what may cause interaction...

Cheers,

Igor.

 

[Joe4]

Try this. Insert the following message box into your VBA code. Then you will see at least which sheet it is having problems with (the last message box you get before the error):

Application.DisplayAlerts = False

For Each Worksheet In Worksheets
    If (Worksheet.Name <> "Instructions") Then
        MsgBox Worksheet.Name
        Worksheet.Delete
    End If
Next

Application.DisplayAlerts = True

Is there anything special about that sheet?
Is this the sheet with your button?

 

[IgorD]

Hi Joe4,

I did what you suggest:
1. It seems always to break at the same sheet, say its called "Start"
2. If I do many copies of "Start" sheet, and try to run the code from one of the copies - it deletes all the copies without problems, but the "Start" itself doesn't work. So to answer you questions - its not always the sheet I am starting macro from;
3. Is there something special? I cant think of anything - it is an empty sheet with four buttons on it. The macros for buttons are like copy a sheet A, or just activating some cells on itself.

Cheers,

Igor.

 

[Joe4]

1. It seems always to break at the same sheet, say its called "Start"

When you same it is always the same sheet, do you mean it is always the same name? Is this name "Start", or is it something else (if so, what?)

Are there any event procedures codes in your workbook? These would be macros/VBA that are in the Worksheet or Workbook modules that are automatically triggered upon some even happening. They could be causing interference.

Other than that, I don't know what else to tell you. I cannot recreate your problem. I created a workbook that has an "Instructions" tab, a "Start" tab, as well as other tabs. I added your VBA code to standard module, and attached it to a command button. It did not seem to matter whether I put the button on "Instructions" tab, the "Start" tab, or any other tab. It worked from wherever I put it.

 

[Tom Urtis]

This will work if the sheet you think is named "Instructions" really is exactly (exactly means exactly, no stray spaces) named "Instructions".

Application.DisplayAlerts = False
Dim ws as worksheet
For Each ws In Worksheets
If ws.Name <> "Instructions" Then ws.Delete
Next
Application.DisplayAlerts = True


What sheet holds the button makes no difference in this case.

 

[IgorD]

Joe4,

With saying always the same sheet, means indeed the same sheet called "Start". Its always the same... Dont know how to describe it better

I dont have any event triggered things in the workbook. Only some macros in sheets and in the module...

I am also lost, as you are. This part of code works in newly created workbooks.

But thanks a lot anyway!

Igor.