'How to delete sheets except for certain sheets in the VBA
I have this code, but excel always crashes when I run it. I don't receive an error code or anything. Excel just closes out.
Sub DeleteSheets()
Dim xWs As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
If xWs.Name <> "Overview" And xWs.Name <> "Models-Features" And xWs.Name <> "Formulas" And xWs.Name <> "Original Features" Then
xWs.Delete
End If
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Solution 1:[1]
It appears your code is deleting all sheets causing the error, even if one is labeled "Overview". I cleaned this up using Select, which now does not delete the appropriate named sheets:
Sub DeleteSheets()
Dim xWs As Worksheet
Application.EnableEvents = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
If Sheets.Count = 1 Then Exit For
Select Case xWs.Name
Case "Overview"
Case "Models-Features"
Case "Formulas"
Case "Original Features"
Case Else
xWs.Delete
End Select
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlAutomatic
End Sub
Edit1: Added check for Sheets.Count which will prevent the error caused by deleting the last sheet in the workbook.
Edit2: Added additional application constraints
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |
