Results 1 to 6 of 6

Thread: Delete sheets if name NOT in list or NOT certain names

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delete sheets if name NOT in list or NOT certain names

    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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,317
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Delete sheets if name NOT in list or NOT certain names

    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 by Michael M; May 1st, 2019 at 07:28 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete sheets if name NOT in list or NOT certain names

    Quote Originally Posted by Michael M View Post
    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

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,317
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Delete sheets if name NOT in list or NOT certain names

    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
    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
    Application.DisplayAlerts = True
    Application.GoTo Worksheets("Master").Range("A1")
        
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete sheets if name NOT in list or NOT certain names

    Works PERFECTLY.

    Thank you Michael!

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,317
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Delete sheets if name NOT in list or NOT certain names

    glad to help....and thx for the feedback..
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •