Delete sheets if name NOT in list or NOT certain names

mr_excel_noob

New Member
Joined
May 1, 2019
Messages
3
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?

Code:
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Maybe this
Code:
For Each ws In Worksheets
    If ws.Name Like "*Template" Or ws.Name Like "*Sh*" Then ws.Delete
Next ws
 
Last edited:
Upvote 0
Maybe this
Code:
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
 
Upvote 0
Ok, try this

Code:
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
 
Upvote 0
glad to help....and thx for the feedback..(y)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top