Hello.

I'm after a little help with how I can run through a list (on a named range) comparing it to the worksheets in the active workbook and delete any worksheets that are not in the workbook AND are not on a list of templates.

I've currently got a function and a sub that will create new worksheets based on the named range and another sub that will delete all worksheets that are not named on a list. I just can't figure out how to merge them, or if it's possible to do it a different way.

Here's what I have:

The function:
Function SheetExists(SheetName As String) As Boolean
' returns TRUE if the sheet exists in the active workbook
 
    SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function
The sub to add worksheets based on the named range:
Sub CreateSheet()
 
    Dim MyCell As Range, MyRange As Range, ws As Worksheet
    Set MyRange = Range("names")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    Set ws = Worksheets("TEMPLATE")
 
Application.ScreenUpdating = False
 
    For Each MyCell In MyRange
        If Not SheetExists("" & MyCell.Value & "") Then
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = MyCell.Value
        ws.Cells.Copy
        Sheets(MyCell.Value).Paste
        ActiveSheet.Cells(2, 2) = MyCell.Value
        Else
        End If
    Next MyCell
 
Worksheets("Sheet1").Select
 
Application.ScreenUpdating = True
End Sub
The sub to delete all worksheets that are not on the list:
Sub deletesheets()

Dim ws As Worksheet
 
Application.DisplayAlerts = False

For Each ws In Worksheets
    If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" And ws.Name <> "Sheet3" And ws.Name <> "Sheet4" And ws.Name <> "Sheet5" And ws.Name <> "TEMPLATE" Then ws.Delete
Next
 
Application.DisplayAlerts = True
End Sub
I think I need to somehow get my named range into the If ws.Name... bit but I can't figure out how.

Thanks for your time and if I've not been clear, please let me know.