Delete Worksheets Named Sheet1, Sheet2, Sheet3, etc.

[Guest]

Some great people helped me out with code similar to that posted below. I
used a slightly modified version of this code to delete a few specific
sheets, and I am now trying to modify it to delete any sheet named Sheet1,
Sheet2, Sheet3, etc. Basically, I have code that loops and builds a workbook
with many sheets that contain information about stocks. Sometimes the loop
spits out sheets named Sheet1, Sheet2, etc. and then it ends. I’m trying to
find a way to delete these few sheets that are created. I just can’t figure
out why the samples of code below will not delete the superfluous sheets.
Does VBA support the * character?


Sub DelSheets()

For Each ws In Worksheets
If ws.Name = "Sheet*" Then ws.Delete
Next ws

For Each ws In Workbooks
If ws.Name Like "Sheet*" Then ws.Delete
Next ws

For Each sh In ActiveWorkbook.Worksheets
If InStr(1, sh.Name, "Sheet*") Then
sh.Delete
End If
Next sh

End Sub

 

[Susan]

i assume you have other worksheets in the workbook that AREN'T named
sheet*???
because you can't delete all the worksheets in a workbook.
you have to have at least one there, and at least one visible.
hth
susan

 

[Guest]

Sub DeleteSheets()
Dim aWS As Worksheet

For Each aWS In ActiveWorkbook.Worksheets
Debug.Print aWS.Name
If aWS.Name Like "Sheet*" Then
aWS.Delete
End If
Next aWS

End Sub

 

[Guest]

Yes, I have several other sheets in the workbook. I am trying to delete only
two or three that come in at the end of the loop. These irrelevant sheets
are always named Sheet1, Sheet2, etc. Sometimes Sheet18, Sheet19, etc. will
be crated if I run the loop several times before closing the workbook.

 

[Guest]

This will delete the sheets with SHeet* without a dialog box

Sub DeleteSheets()
Dim aWS As Worksheet

For Each aWS In ActiveWorkbook.Worksheets
Debug.Print aWS.Name
If aWS.Name Like "Sheet*" Then
Application.DisplayAlerts = False
aWS.Delete
Application.DisplayAlerts = True
End If
Next aWS

End Sub

 

[Guest]

Excellent!! That's exactly it!! How come my version did not work Bara? Did
it have something to do with the ".Name Like "Sheet*" Then"?

 

[Guest]

First, you have to declare ws (or aws) as a worksheet
And you need
For each aws in Activeworkbook.worksheets

I don't think you had that in some.

 

[Guest]

Ahhhh! I read your response, reviewed my post, and realized that you are
correct. I didn’t have a Dim ws As Worksheet. Thanks so much!!