Delete worksheet in collection

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

 

[Dave Peterson]

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?