Delete worksheet in Excel using VBA

Question

I have a macros that generates a number of workbooks. I would like the macros, at the start of the run, to check if the file contains 2 spreadsheets, and delete them if they exist.

The code I tried was:

If Sheet.Name = "ID Sheet" Then     Application.DisplayAlerts = False     Sheet.Delete     Application.DisplayAlerts = True  End If  If Sheet.Name = "Summary" Then     Application.DisplayAlerts = False     Sheet.Delete     Application.DisplayAlerts = True End If 

This code is returning an error:

run time error #424, object required.

I probably have the wrong formatting, but if there is an easier way to do this, it would be very useful.

Answer 1

Consider:

Sub SheetKiller()     Dim s As Worksheet, t As String     Dim i As Long, K As Long     K = Sheets.Count      For i = K To 1 Step -1         t = Sheets(i).Name         If t = "ID Sheet" Or t = "Summary" Then             Application.DisplayAlerts = False                 Sheets(i).Delete             Application.DisplayAlerts = True         End If     Next i End Sub 

NOTE:

Because we are deleting, we run the loop backwards.

Answer 2

Try this code:

For Each aSheet In Worksheets      Select Case aSheet.Name          Case "ID Sheet", "Summary"             Application.DisplayAlerts = False             aSheet.Delete             Application.DisplayAlerts = True      End Select  Next aSheet