Prevent Duplicate Sheets when Adding using VBA

In your loop, check if worksheet name already exist against value.

You can use a COUNT.IF function applied to the list down to the point you are currently focusing, using the content of your focused cell as criteria. Let's say you are currently focusing cell A4. The formula could be something like:

=COUNTIF($A$2:A4,A4)

Integrated in a code in VBA, it could become something like this:

Option Explicit

Sub SubTest()
    
    Dim DblRow As Double
    Dim RngList As Range
    
    Set RngList = Range("A2:A10")
    
    For DblRow = 1 To RngList.Rows.Count
        
        If Excel.WorksheetFunction.CountIf(Range(RngList(1, 1), RngList(DblRow, 1)), RngList(DblRow, 1).Value2) = 1 Then
            
            'Do stuff
            
        Else
            
            'Do nothing/something else
            
        End If
        
    Next
    
End Sub

You could get a unique list of the values in an array, then loop through that to add the new sheets.

Sub AddSheets()
Dim wsNew As Worksheet
Dim rng As Range
Dim idx As Long
Dim varNames As Variant

    ' assumes column A on Sheet1 has the values with the new worksheet names
    With Sheets("Sheet1")

        Set rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
        
    End With
    
    ' get unique values from column A in a 1D array
    varNames = Application.Transpose(Application.Unique(rng))
    
    ' loop through array, add new sheets and name them
    For idx = LBound(varNames) To UBound(varNames)
        With ThisWorkbook
            Set wsNew = .Sheets.Add(after:=.Sheets(.Sheets.Count))
            wsNew.Name = varNames(idx)
        End With
    Next idx
 
End Sub