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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,803
Office Version
2013
Platform
Windows
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:

mr_excel_noob

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,803
Office Version
2013
Platform
Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,803
Office Version
2013
Platform
Windows
glad to help....and thx for the feedback..(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,096,445
Messages
5,450,490
Members
405,614
Latest member
SJ789

This Week's Hot Topics

Top