How to create different sheets based on the inputs in another sheet?

[novicecoder]

I've created a macro which is supposed to take inputs from a certain sheet, as in "Docs" from a workbook and create as many new worksheets as many inputs are there. In my case, I've been trying to create three new sheets with this three inputs "AB","BC" and "CD" which are in Range("A1") to Range("A3") in column A. However, when I run my script, It only creates a new sheet with the name "AB" whereas I was expecting to have three new sheets with the three different names. The important thing is the script will first look for the three sheets whether they already exist. If they are not then the macro will create them. Where I'm going wrong with my following attempt and how can I fix it?

This is the macro I've written:

Sub NameNewSheet()
    Dim ws As Worksheet, shtname As Range

    For Each shtname In Sheets("Docs").Range("A1:A" & Sheets("Docs").Range("A" & Rows.Count).End(xlUp).row)

        With ThisWorkbook
            On Error Resume Next
            Set ws = .Sheets(shtname)
            On Error GoTo 0

            If ws Is Nothing Then
                Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
                ws.Name = shtname
            End If
        End With
    Next shtname
End Sub

 

[Logit]

Try this. You'll need to change the sheet name.

Option Explicit

Sub CreateSheetsFromAList()
        Dim MyCell As Range, MyRange As Range
        Application.ScreenUpdating = False
       
        Set MyRange = Sheets("Master List").Range("A1")
        Set MyRange = Range(MyRange, MyRange.End(xlDown))
       
       
        For Each MyCell In MyRange
            Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
           
        Next MyCell
        Application.ScreenUpdating = True
        Sheets("Master List").Activate
        Sheets("Master List").Range("A1").Select
End Sub

 

[novicecoder]

Either I could not make my description clear or you failed to understand what I meant @Logit. It is not about creating new sheets and naming them only. The problem starts when I re-run the script. My question was how can I create my script in such a way so that it will first look for the sheets whether they exist already. If the sheets with the names are not there then it only create new sheets and name them no matter how many times I run the script. Thanks.

 

[Logit]

Try this :

Option Explicit

Sub DoesTheSheetExists()
Dim MyCell As Range, MyRange As Range
Dim ws As Worksheet
       
Application.ScreenUpdating = False
     
Set MyRange = Sheets("Sheet1").Range("A1")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
     
    For Each MyCell In MyRange
        If SheetExist(MyCell.Value) Then
            GoTo FindNext:
        Else
            Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
        End If
FindNext:
    Next MyCell
       
    Application.ScreenUpdating = True
    Sheets("Sheet1").Activate
    Sheets("Sheet1").Range("A1").Select
     
   
End Sub

Function SheetExist(strSheetName As String) As Boolean
    Dim i As Integer

    For i = 1 To Worksheets.Count
        If Worksheets(i).Name = strSheetName Then
            SheetExist = True
            Exit Function
        End If
    Next i
End Function