Delete sheets if name NOT in list or NOT certain names

[mr_excel_noob]

Using multiple questions & answers in this board, I've put together the following code to:

1. create new sheets based on a list (range B11:B in "Master" sheet),
2. each sheet copied from a Template and renamed from the list,
3. skip names with a sheet already made,
4. delete sheets named "*Template (*" as they are created for some reason, and
5. Return to "Master" sheet

Now I need to add code to delete any sheets who's name isn't in the list [Range B11:B on "Master" Sheet] (i.e. if a row gets deleted), without also deleting sheets named "Master", "Template" and "Sheet2" as they are not in the list

Sorry I've probably explained poorly. Can anyone help?

Sub CreateAndNameWorksheets()
    Dim c As Range
    
    Application.ScreenUpdating = False
    For Each c In Sheets("Master").Range("B11:B" & Sheets("Master").Range("B" & Rows.Count).End(xlUp).Row)
        Sheets("Template").Copy after:=Sheets(Sheets.Count)
        With c
            On Error Resume Next
            ActiveSheet.Name = .Value
            ActiveSheet.Protect
            On Error GoTo 0
            
        End With
    Next c
    Application.ScreenUpdating = True
    
    Application.DisplayAlerts = False
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
    If ws.Name Like "*Template (*" Then ws.Delete
    Next ws
    Application.DisplayAlerts = True
    
    Application.GoTo Worksheets("Master").Range("A1")
    
End Sub

 

[Michael M]

Maybe this

For Each ws In Worksheets
    If ws.Name Like "*Template" Or ws.Name Like "*Sh*" Then ws.Delete
Next ws

 

[mr_excel_noob]

Maybe this

For Each ws In Worksheets
    If ws.Name Like "*Template" Or ws.Name Like "*Sh*" Then ws.Delete
Next ws

Thanks for your time Michael. However, I'm looking delete any sheets:

1. not named in the list ("Master" sheet range B11:B) or
2. not named "Template" "Master" or "Sheet2".

If I understand correctly, I think your code is deleting any sheets named Template or Sheets

 

[Michael M]

Ok, try this

Sub CreateAndNameWorksheets()
    Dim c As Range, lr As Long, ms As Object
    
    Application.ScreenUpdating = False
    For Each c In Sheets("Master").Range("B11:B" & Sheets("Master").Range("B" & Rows.Count).End(xlUp).Row)
        Sheets("Template").Copy after:=Sheets(Sheets.Count)
        With c
            On Error Resume Next
            ActiveSheet.Name = .Value
            ActiveSheet.Protect
            On Error GoTo 0
            
        End With
    Next c
    Application.ScreenUpdating = True
[color=red]Set ms = Sheets("Master")
lr = ms.Cells(Rows.Count, "B").End(xlUp).Row
Application.DisplayAlerts = False
For Each ws In Worksheets
    If IsError(Application.Match(ws.Name, ms.Range("B11:B" & lr), 0)) And ws.Name <> "Template" And ws.Name <> "Master" And ws.Name <> "Sheet2" Then
        ws.Delete
    End If
Next ws[/color]
Application.DisplayAlerts = True
Application.GoTo Worksheets("Master").Range("A1")
    
End Sub

 

[mr_excel_noob]

Works PERFECTLY.

Thank you Michael!

 

[Michael M]

glad to help....and thx for the feedback..