I need to conditionally delete certain sheets based on name. Here's my code:
Dim mWorksheet As Worksheet

'Process worksheets
For Each mWorksheet In Worksheets
mWorksheet.Select
'Application.DisplayAlerts = False
If mWorksheet.Name = "Milestone Summary" Then mWorksheet.Delete
If mWorksheet.Name = "Columns Template" Then mWorksheet.Delete
'Application.DisplayAlerts = True
If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
Next mWorksheet

I get a non-descript application error on the deletions, I think because
when I delete a particular worksheet it changes the number of worksheets in
the Worksheets collection, which screws up the counting process.

Is there an easy alternative? Or, do I need to go through the collection
first remembering the sheet names I need to later delete?

Stu,

Try this:-

Sub stantial()

'Process worksheets
For Each mworksheet In Worksheets
mworksheet.Select
'Application.DisplayAlerts = False
If mworksheet.Name = "Milestone Summary" Or _
mworksheet.Name = "Columns Template" Then _
mworksheet.Delete
GoTo 100
'Application.DisplayAlerts = True
'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
100 Next mworksheet

End Sub
Sub stantial()
'Process worksheets
For Each mworksheet In Worksheets
mworksheet.Select
'Application.DisplayAlerts = False
If mworksheet.Name = "Milestone Summary" Or _
mworksheet.Name = "Columns Template" Then _
mworksheet.Delete
'Application.DisplayAlerts = True
'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
Next mworksheet
End Sub

Mike

"Stu W" wrote:

> I need to conditionally delete certain sheets based on name. Here's my code:
> Dim mWorksheet As Worksheet
>
> 'Process worksheets
> For Each mWorksheet In Worksheets
> mWorksheet.Select
> 'Application.DisplayAlerts = False
> If mWorksheet.Name = "Milestone Summary" Then mWorksheet.Delete
> If mWorksheet.Name = "Columns Template" Then mWorksheet.Delete
> 'Application.DisplayAlerts = True
> If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> Next mWorksheet
>
> I get a non-descript application error on the deletions, I think because
> when I delete a particular worksheet it changes the number of worksheets in
> the Worksheets collection, which screws up the counting process.
>
> Is there an easy alternative? Or, do I need to go through the collection
> first remembering the sheet names I need to later delete?

Stu,

pasting ***up. It's this one:-

Sub stantial()
'Process worksheets
For Each mworksheet In Worksheets
mworksheet.Select
'Application.DisplayAlerts = False
If mworksheet.Name = "Milestone Summary" Or _
mworksheet.Name = "Columns Template" Then _
mworksheet.Delete
'Application.DisplayAlerts = True
'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
Next mworksheet
End Sub


"Mike H" wrote:

> Stu,
>
> Try this:-
>
> Sub stantial()
>
> 'Process worksheets
> For Each mworksheet In Worksheets
> mworksheet.Select
> 'Application.DisplayAlerts = False
> If mworksheet.Name = "Milestone Summary" Or _
> mworksheet.Name = "Columns Template" Then _
> mworksheet.Delete
> GoTo 100
> 'Application.DisplayAlerts = True
> 'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> 100 Next mworksheet
>
> End Sub
> Sub stantial()
> 'Process worksheets
> For Each mworksheet In Worksheets
> mworksheet.Select
> 'Application.DisplayAlerts = False
> If mworksheet.Name = "Milestone Summary" Or _
> mworksheet.Name = "Columns Template" Then _
> mworksheet.Delete
> 'Application.DisplayAlerts = True
> 'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> Next mworksheet
> End Sub
>
> Mike
>
> "Stu W" wrote:
>
> > I need to conditionally delete certain sheets based on name. Here's my code:
> > Dim mWorksheet As Worksheet
> >
> > 'Process worksheets
> > For Each mWorksheet In Worksheets
> > mWorksheet.Select
> > 'Application.DisplayAlerts = False
> > If mWorksheet.Name = "Milestone Summary" Then mWorksheet.Delete
> > If mWorksheet.Name = "Columns Template" Then mWorksheet.Delete
> > 'Application.DisplayAlerts = True
> > If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> > Next mWorksheet
> >
> > I get a non-descript application error on the deletions, I think because
> > when I delete a particular worksheet it changes the number of worksheets in
> > the Worksheets collection, which screws up the counting process.
> >
> > Is there an easy alternative? Or, do I need to go through the collection
> > first remembering the sheet names I need to later delete?

Thanks, Mike. When I first saw your GoTo line, I thought "Yikes" :-)

I guess I need to go through one run through the collection to delete the
"Milestone Reporting" and "Columns Template" worksheets, and then run through
the collection again to process the "Status" sheets. But that's not too bad.

And it works. This is a great community, Mike, and your participation in it
is well appreciated!

"Mike H" wrote:

> Stu,
>
> pasting ***up. It's this one:-
>
> Sub stantial()
> 'Process worksheets
> For Each mworksheet In Worksheets
> mworksheet.Select
> 'Application.DisplayAlerts = False
> If mworksheet.Name = "Milestone Summary" Or _
> mworksheet.Name = "Columns Template" Then _
> mworksheet.Delete
> 'Application.DisplayAlerts = True
> 'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> Next mworksheet
> End Sub
>
>
> "Mike H" wrote:
>
> > Stu,
> >
> > Try this:-
> >
> > Sub stantial()
> >
> > 'Process worksheets
> > For Each mworksheet In Worksheets
> > mworksheet.Select
> > 'Application.DisplayAlerts = False
> > If mworksheet.Name = "Milestone Summary" Or _
> > mworksheet.Name = "Columns Template" Then _
> > mworksheet.Delete
> > GoTo 100
> > 'Application.DisplayAlerts = True
> > 'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> > 100 Next mworksheet
> >
> > End Sub
> > Sub stantial()
> > 'Process worksheets
> > For Each mworksheet In Worksheets
> > mworksheet.Select
> > 'Application.DisplayAlerts = False
> > If mworksheet.Name = "Milestone Summary" Or _
> > mworksheet.Name = "Columns Template" Then _
> > mworksheet.Delete
> > 'Application.DisplayAlerts = True
> > 'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> > Next mworksheet
> > End Sub
> >
> > Mike
> >
> > "Stu W" wrote:
> >
> > > I need to conditionally delete certain sheets based on name. Here's my code:
> > > Dim mWorksheet As Worksheet
> > >
> > > 'Process worksheets
> > > For Each mWorksheet In Worksheets
> > > mWorksheet.Select
> > > 'Application.DisplayAlerts = False
> > > If mWorksheet.Name = "Milestone Summary" Then mWorksheet.Delete
> > > If mWorksheet.Name = "Columns Template" Then mWorksheet.Delete
> > > 'Application.DisplayAlerts = True
> > > If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> > > Next mWorksheet
> > >
> > > I get a non-descript application error on the deletions, I think because
> > > when I delete a particular worksheet it changes the number of worksheets in
> > > the Worksheets collection, which screws up the counting process.
> > >
> > > Is there an easy alternative? Or, do I need to go through the collection
> > > first remembering the sheet names I need to later delete?

Why not just delete those sheets before you start?

application.displayalerts = false 'stop any prompt
on error resume next 'in case they don't exist
worksheets("Milestone Summary").delete
worksheets("Columns Template").delete
on error goto 0
application.displayalerts = true

Then just do your status worksheet stuff. Why loop?

Stu W wrote:
>
> Thanks, Mike. When I first saw your GoTo line, I thought "Yikes" :-)
>
> I guess I need to go through one run through the collection to delete the
> "Milestone Reporting" and "Columns Template" worksheets, and then run through
> the collection again to process the "Status" sheets. But that's not too bad.
>
> And it works. This is a great community, Mike, and your participation in it
> is well appreciated!
>
> "Mike H" wrote:
>
> > Stu,
> >
> > pasting ***up. It's this one:-
> >
> > Sub stantial()
> > 'Process worksheets
> > For Each mworksheet In Worksheets
> > mworksheet.Select
> > 'Application.DisplayAlerts = False
> > If mworksheet.Name = "Milestone Summary" Or _
> > mworksheet.Name = "Columns Template" Then _
> > mworksheet.Delete
> > 'Application.DisplayAlerts = True
> > 'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> > Next mworksheet
> > End Sub
> >
> >
> > "Mike H" wrote:
> >
> > > Stu,
> > >
> > > Try this:-
> > >
> > > Sub stantial()
> > >
> > > 'Process worksheets
> > > For Each mworksheet In Worksheets
> > > mworksheet.Select
> > > 'Application.DisplayAlerts = False
> > > If mworksheet.Name = "Milestone Summary" Or _
> > > mworksheet.Name = "Columns Template" Then _
> > > mworksheet.Delete
> > > GoTo 100
> > > 'Application.DisplayAlerts = True
> > > 'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> > > 100 Next mworksheet
> > >
> > > End Sub
> > > Sub stantial()
> > > 'Process worksheets
> > > For Each mworksheet In Worksheets
> > > mworksheet.Select
> > > 'Application.DisplayAlerts = False
> > > If mworksheet.Name = "Milestone Summary" Or _
> > > mworksheet.Name = "Columns Template" Then _
> > > mworksheet.Delete
> > > 'Application.DisplayAlerts = True
> > > 'If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> > > Next mworksheet
> > > End Sub
> > >
> > > Mike
> > >
> > > "Stu W" wrote:
> > >
> > > > I need to conditionally delete certain sheets based on name. Here's my code:
> > > > Dim mWorksheet As Worksheet
> > > >
> > > > 'Process worksheets
> > > > For Each mWorksheet In Worksheets
> > > > mWorksheet.Select
> > > > 'Application.DisplayAlerts = False
> > > > If mWorksheet.Name = "Milestone Summary" Then mWorksheet.Delete
> > > > If mWorksheet.Name = "Columns Template" Then mWorksheet.Delete
> > > > 'Application.DisplayAlerts = True
> > > > If WorksheetType(mWorksheet) = "Status" Then ProcessWorkSheet
> > > > Next mWorksheet
> > > >
> > > > I get a non-descript application error on the deletions, I think because
> > > > when I delete a particular worksheet it changes the number of worksheets in
> > > > the Worksheets collection, which screws up the counting process.
> > > >
> > > > Is there an easy alternative? Or, do I need to go through the collection
> > > > first remembering the sheet names I need to later delete?

--

Dave Peterson