Delete sheet if exists in Excel with VBA

March 1, 2022

Sub DeleteSheets2()

Application.DisplayAlerts = False

For Each WS In Worksheets
    If WS.Name = "HIT_Qualys_Scan" Then
        Sheets("HIT_Qualys_Scan").Delete
    End If
    
    If WS.Name = "Status" Then
        Sheets("Status").Delete
    End If
    
Next WS

Application.DisplayAlerts = True

End Sub

The above works but I always end up getting an Error that I have to End and then run the Sub again.

I get:

with line:

If WS.Name = "Status" Then

Once I hit "End" and run it again, it works. Is there a way to avoid this error? I am confused why it runs the 2nd time once I hit End.

>Solution :

The reason is that if WS is on sheet HIT_Qualys_Scan then you delete it with that first If statement. WS at that point is nothing because its object is deleted.

So when you do If WS.Name = "Status" Then it freaks out. There is no WS.Name anymore. It can’t determine the property Name of an object that doesn’t exist.

Instead test for both sheet names and delete by referring to WS:

Sub DeleteSheets2()

Application.DisplayAlerts = False

For Each WS In Worksheets
    If WS.Name = "HIT_Qualys_Scan" OR WS.Name = "Status" Then
        WS.Delete
    End If     
    
Next WS

Application.DisplayAlerts = True

End Sub