VBA Delete Multiple Worksheets

[bobgodfrey]

I have a workbook that users add sheets to, and save and the workbook just get's junked up. I know you can use VBA to delete a sheet, just by simply executing

For Each ws In ActiveWorkbook.Worksheets
  ws.Delete
Next ws

But that is slow! What I am after is to delete ANY sheet that is not named "Master Data" or "Holding Pattern" in the workbook.

[jo15765]

Not sure if this will provide any speed increase or not, but the syntax I always use to exclude a worksheet or two when needing to delete is

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Holding Pattern" Or ws.Name <> "Master Data" Then
        ws.Delete
    End If
Next ws

I think the sluggishness comes into play when your workbook has a large number of sheets in it, as this syntax above iterates over each worksheet individually.

[Sintek]

Hi Jo15765
You can add

Application.ScreenUpdating = False 
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Holding Pattern" Or ws.Name <> "Master Data" Then
        ws.Delete
    End If
Next ws
Application.ScreenUpdating = True

[Norie]

Try this.

    Application.DisplayAlerts = False

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Holding Pattern" And ws.Name <> "Master Data" Then
            ws.Delete
        End If
    Next ws

    Application.DisplayAlerts = True