XLA routines: EE_DeleteSheet

Nick's picture
Deleting a sheet using VBA is more work than it should be. Here's a routine that accepts either a sheet name as string or a worksheet.
Sub EE_DeleteSheet(sht As Variant)
    Dim blnDisplayAlerts    As Boolean
    Dim wks                 As Object
    Dim wbk                 As Workbook
 
' http://excelexperts.com/xla-routines-eedeletesheet
    
    Set wbk = ActiveWorkbook
 
    On Error Resume Next
    Select Case TypeName(sht)
        Case "String"
            Set wks = wbk.Worksheets(CStr(sht))
        Case "Worksheet", "Sheet"
            Set wks = sht
    End Select
    Err.Clear: On Error GoTo 0: On Error GoTo -1
 
    blnDisplayAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
 
    If Not wks Is Nothing Then
        wks.Delete
    End If
 
    Application.DisplayAlerts = blnDisplayAlerts
    Set wks = Nothing
    Set wbk = Nothing
End Sub