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.
CodePudding user response:
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