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

try this within your if statements:

Application.DisplayAlerts = False
Worksheets(“Sheetname”).Delete
Application.DisplayAlerts = True

Answer 2

Worksheets("Sheet1").Delete
Worksheets("Sheet2").Delete

Answer 3

You could use On Error Resume Next then there is no need to loop through all the sheets in the workbook.

With On Error Resume Next the errors are not propagated, but are suppressed instead. So here when the sheets does't exist or when for any reason can't be deleted, nothing happens. It is like when you would say : delete this sheets, and if it fails I don't care. Excel is supposed to find the sheet, you will not do any searching.

Note: When the workbook would contain only those two sheets, then only the first sheet will be deleted.

Dim book
Dim sht as Worksheet

set book= Workbooks("SomeBook.xlsx")

On Error Resume Next

Application.DisplayAlerts=False 

Set sht = book.Worksheets("ID Sheet")
sht.Delete

Set sht = book.Worksheets("Summary")
sht.Delete

Application.DisplayAlerts=True 

On Error GoTo 0

Answer 4

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 5

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