Solution to delete worksheet based on worksheet name
I am providing the solution for the “Deluxe-Plus” version in the below code
To determine which worksheet is to delete, create an array called delWS(), and then loop through all the worksheet name to find the worksheet name that contains “DELETE” or “OLD”.
If (InStr(WS.Name, "DELETE") <> 0 Or InStr(WS.Name, "OLD") <> 0) And InStr(WS.Name, "KEEP") = 0
Instr function is to check whether the worksheet name contains the key words, the function returns the starting position of the key word in the worksheet name; return 0 if not found.
Application.DisplayAlerts = False is used to prevent alert message before deleting worksheet.
Public Sub deluxPlus() Dim delWS() As Variant Dim WS As Worksheet 'To store all the unwanted worksheets in array delWS() For Each WS In ThisWorkbook.Worksheets If (InStr(WS.Name, "DELETE") <> 0 Or InStr(WS.Name, "OLD") <> 0) And InStr(WS.Name, "KEEP") = 0 Then Count = Count + 1 ReDim Preserve delWS(Count) delWS(Count) = WS.Name End If Next WS 'To save all the unwanted worksheets name in variable alldelWS If Count > 0 Then For i = 1 To Count alldelWS = alldelWS & "," & delWS(i) Next i End If alldelWS = Right(alldelWS, Len(alldelWS) - 1) 'To delete all worksheets in array delWS() If Count > 0 Then x = MsgBox("Are you sure you wanna delete them? " & vbNewLine & alldelWS, vbOKCancel) If x = vbOK Then For i = 1 To Count Application.DisplayAlerts = False Worksheets(delWS(i)).Delete Application.DisplayAlerts = True Next i End If End If End Sub