Error Handling When Deleting Sheets

[mayoung]

I have two sets of code one for deleting one sheet and another for deleting multiple sheets the code works but the error handler does not work when there are no sheet or sheets, which I need. Any ideas how to modify this so the error handler works?

'Private Sub DeleteActiveSheetSummary(ctrl As IRibbonControl)
Sub DeleteActiveSheetSummary()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    If ActiveWorkbook.Name = "ServiceFile.xml" Then
        
        On Error GoTo EH:
      
        With ActiveSheet
           Application.DisplayAlerts = False
            If LCase(.Name) Like "*sheet*" Then .Delete
           Application.DisplayAlerts = True
        End With
        
        ActiveWindow.ScrollWorkbookTabs Sheets:=-4
        
        Sheets("WIP-SUMMARY").Select
        
        GoTo EJ:
EH:

        MsgBox "No Sheet to Delete", vbCritical
EJ:
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Exit Sub
        
    Else
        MsgBox "Service File Is Not Active Or Has Not Been Uploaded.", vbCritical
    End If
    
End Sub

'Private Sub DeleteSheetsSummary(ctrl As IRibbonControl)
Sub DeleteSheetsSummary()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    If ActiveWorkbook.Name = "ServiceFile.xml" Then
        
        On Error GoTo EH:
        
        Dim wsheet As Worksheet
        For Each wsheet In ActiveWorkbook.Worksheets
            If UCase(wsheet.Name) Like "*SHEET*" Then
                Application.DisplayAlerts = False
                wsheet.Delete
                Application.DisplayAlerts = True
            End If
        Next
        
        ActiveWindow.ScrollWorkbookTabs Sheets:=-4
        
        Sheets("WIP-SUMMARY").Select
        
        GoTo EJ:
EH:
        MsgBox "No Sheets To Delete", vbCritical
EJ:
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Exit Sub
        
    Else
        MsgBox "Service File Is Not Active Or Has Not Been Uploaded.", vbCritical
    End If
    
End Sub

 

[StephenCrump]

If there is no matching sheet to be deleted, then your code won't generate an error.

Instead, you could increment a count for each sheet you delete, and then test if count = 0.

 

[mayoung]

If there is no matching sheet to be deleted, then your code won't generate an error.

Instead, you could increment a count for each sheet you delete, and then test if count = 0.

Not Sure how to apply this?

 

[StephenCrump]

At its simplest:

Dim wsheet As Worksheet
Dim count As Long

For Each wsheet In ActiveWorkbook.Worksheets
    If UCase(wsheet.Name) Like "*SHEET*" Then
        Application.DisplayAlerts = False
        wsheet.Delete
        count = count + 1
        Application.DisplayAlerts = True
    End If
Next

MsgBox count & " sheet" & IIf(count = 1, "", "s") & " deleted", vbInformation

Or if you want to take different actions depending on the value of count:

'....

If count = 0 Then
    'No sheets deleted
    'Do something?
Else
    'Do something different?
End If

 

[mayoung]

At its simplest:

Dim wsheet As Worksheet
Dim count As Long

For Each wsheet In ActiveWorkbook.Worksheets
    If UCase(wsheet.Name) Like "*SHEET*" Then
        Application.DisplayAlerts = False
        wsheet.Delete
        count = count + 1
        Application.DisplayAlerts = True
    End If
Next

MsgBox count & " sheet" & IIf(count = 1, "", "s") & " deleted", vbInformation

Or if you want to take different actions depending on the value of count:

'....

If count = 0 Then
    'No sheets deleted
    'Do something?
Else
    'Do something different?
End If

Thank You Very Much..