Delete worksheets and the code sheets remain?

[Mike]

I used the following code to delete all the sheets except Sheets("Dummy").
It does this successfully for about 10 of the sheets, but there are 5 other
sheets whose actual WorkSheets ARE deleted but their CODE sheets remain.
What I see in the VBAProject window BEFORE deletion is "Sheet1( sheetxyz)"
and then AFTER the deletion I see "Sheet1". Only one of the code sheets
actually contained code.

Any ideas??





Dim wb As Workbook

Dim oSheet As Object

Set wb = ActiveWorkbook

For Each oSheet In wb.Sheets

If oSheet.Name <> "Dummy" Then

Sheets(oSheet.Name).Select

ActiveWindow.SelectedSheets.Delete

End If

Next

 

[Vasant Nanavati]

I can't replicate your problem, but try cleaning up the code a bit as
follows:

Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Dummy" Then ws.Delete
Next
Application.DisplayAlerts = True

Also, are you by any chance trying to run this code from a worksheet code
module? It should be run from a standard module.

 

[Mike]

Thanks for responding Vasant.
I am running the code from a Forms Module. Not sure if that's the right
terminology, but it's the module where all the event handlers are located.
The problem seems to be an intermittent type thing. When it does go bad, it
seems to stay bad for a long time, and then all of a sudden it's good. I
always start with the same TEST workbook and always exit Excel before making
another attempt. I'll try your cleanup code the next time it goes bad.
thanks