Adding, Deleting worksheets based on a List included in the workbook

[Alroj]

Hi Community,

I found this macro below which I adjusted to include sheets to my workbook. The sheets are named using the text (January, February, March) included in column A of the sheet called "List".
How could this be adjusted so:

1. When new names are added to the "List", the macro includes worksheets for the new names e.g. April, May
2. The macro does not double up with the ones already included e.g. January, February, March
3. The macro deletes the sheets that have been excluded from the "List"? e.g. January no longer included in the "List"

Your assistance would be greatly appreciated

Sub AAACreateSheets()
  Dim c As Range
  Dim Ws As Worksheet

  With Sheets("List")
    For Each c In .Range("A2", .Range("A" & Rows.Count).End(3))
      If c.Value <> "" Then
        Sheets.Add After:=Sheets(.Name)
         ActiveSheet.Name = c.Value
      End If
    Next
  End With

End Sub

 

[myall_blues]

Hi.

#1 your code already handles.
#2 and 3 - try this.

Sub AAACreateSheets()
    Dim c As Range, i As Long, bReturn As Boolean, sDel As Boolean
    Dim Ws As Worksheet
   
    With Sheets("List")
        For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        '
        ' Add any sheets in the list if they don't already exist
        '
            If c.Value <> "" Then
                If Not CheckSheet(c) Then
                    Sheets.Add After:=Sheets(.Name)
                    ActiveSheet.Name = c
                End If
            End If
        Next c
    End With
    '
    ' Remove any sheets not in the list
    '
    For i = Sheets.Count To 1 Step -1
        sDel = True
        For Each c In Sheets("List").Range("A2", Sheets("List").Range("A" & Rows.Count).End(xlUp))
            If c = Sheets(i).Name Or Sheets(i).Name = "List" Then
                sDel = False
                Exit For
            End If
        Next c
        If sDel = True Then
            Application.DisplayAlerts = False
            Sheets(i).Delete
            Application.DisplayAlerts = True
        End If
    Next i
End Sub
'
Public Function CheckSheet(ByVal sSheetName As String) As Boolean
    '
    ' Check required worksheet exists
    '
    Dim bReturn As Boolean
    Dim i As Integer
    For i = 1 To Sheets.Count
        If Sheets(i).Name = sSheetName Then
            bReturn = True
            Exit For
        End If
    Next i
    CheckSheet = bReturn
End Function

 

[Alroj]

Hi myall_blues,

Your macro worked very well!!
I had to add more sheets in the section:
If c = Sheets(i).Name Or Sheets(i).Name = "List"
so the sheets I want to keep are not deleted.

Thank you very much !!!!!!

 

[myall_blues]

You’re welcome. I’m glad it worked for you.