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

--
RyGuy

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


On Apr 6, 2:52 pm, ryguy7272 <ryguy7...@discussions.microsoft.com>
wrote:
> 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
>
> --
> RyGuy

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
"ryguy7272" wrote:

> 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
>
> --
> RyGuy

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.



--
RyGuy


"Susan" wrote:

> 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
>
>
> On Apr 6, 2:52 pm, ryguy7272 <ryguy7...@discussions.microsoft.com>
> wrote:
> > 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
> >
> > --
> > RyGuy
>
>
>

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


"ryguy7272" wrote:

> 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.
>
>
>
> --
> RyGuy
>
>
> "Susan" wrote:
>
> > 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
> >
> >
> > On Apr 6, 2:52 pm, ryguy7272 <ryguy7...@discussions.microsoft.com>
> > wrote:
> > > 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
> > >
> > > --
> > > RyGuy
> >
> >
> >

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"?

--
RyGuy


"Barb Reinhardt" wrote:

> 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
> "ryguy7272" wrote:
>
> > 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
> >
> > --
> > RyGuy

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.

"ryguy7272" wrote:

> 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"?
>
> --
> RyGuy
>
>
> "Barb Reinhardt" wrote:
>
> > 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
> > "ryguy7272" wrote:
> >
> > > 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
> > >
> > > --
> > > RyGuy

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!!
--
RyGuy


"Barb Reinhardt" wrote:

> 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.
>
> "ryguy7272" wrote:
>
> > 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"?
> >
> > --
> > RyGuy
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > 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
> > > "ryguy7272" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > RyGuy