Select & Delete Sheets in Workbook using Wildcard

[deve8ore]

Hello,

I have an Excel workbook with ~ 21 sheets in it.
Let's say 7 are named "Red1", "Red2", "Red3", ect....
Another 7 are named for "Green1, 2, 3", ect...
and the last 7 are named "Blue1, 2, 3", ect...

I'm trying to write a very basic code to go though the entire Workbook, select and then delete all sheets with the name containing "Red*" (regardless of the following number). So far I have:

Sub DeleteRed()
Dim ws As Worksheet
Dim x As Integer
x = 1
For Each ws In ActiveWorkbook.Sheets
x = x + 1
For Each ws In ActiveWorkbook.Sheets
If Left(ws.Name, 3) = "Red" Then
ActiveSheet.Delete
End If
Next ws
End Sub

Yes I am a novice, and any help would be appreciated!

Thank you.

[MikeTheBike]

Hi

On the basis that the 'Colour' (or any other string) is always at the begining of the sheet name, then this will delete any name series you care to create.

Note: this will delete sheets without notification. If that is a problem just delete the line
Application.DisplayAlerts = False
from the code

Also it will not delete the last sheet (it will throw an error if you try and delete every sheet).

The UCase() functions make this not case sensitive, again remove these if you want case sensitivity.

Sub DeleteSheetColour(ByVal strColour As String)
    Dim ws As Worksheet
    Dim NLen As Integer
 
    NLen = Len(strColour)
    For Each ws In ActiveWorkbook.Sheets
        With ws
            If Left(UCase(.Name), NLen) = UCase(strColour) Then
                If ThisWorkbook.Sheets.Count > 1 Then
                    Application.DisplayAlerts = False
                    .Delete
                    Application.DisplayAlerts = True
                End If
            End If
        End With
    Next ws
End Sub
 
Sub TestSheetDelete()
    DeleteSheetColour "Green"
End Sub

HTH

MTB

[deve8ore]

Thanks.... I appreciate your help!!