[Solved]-Adding Sheets With Sequential Names Stops Incrementing After the 10th Sheet-VBA Excel

Add Worksheets With Sequential Names

Compact

Sub GetAvailableSheetName()
    On Error GoTo ClearError
    
    Dim ws As Worksheet
    Dim n As Long
    Dim wsName As String
    
    Do
        n = n + 1
        wsName = "Combined-" & n
        Set ws = ThisWorkbook.Worksheets(wsName)
    Loop
        
WorksheetNaming:
    On Error Resume Next
    ThisWorkbook.Worksheets .Add(After:=ThisWorkbook _
        .Sheets(ThisWorkbook.Sheets.Count)).Name = wsName
    On Error GoTo 0
    
    Exit Sub

ClearError:
    Resume WorksheetNaming
End Sub

Argumented

Sub AddSequentialSheetNameTEST()
    AddSequentialSheetName ThisWorkbook, "Combined-"
    MsgBox "Added the worksheet '" & ActiveSheet.Name, vbInformation
End Sub


Sub AddSequentialSheetName( _
        ByVal wb As Workbook, _
        Optional ByVal Prefix As String = "Sheet", _
        Optional ByVal Suffix As String = "")
    On Error GoTo ClearError
    
    Dim ws As Worksheet
    Dim n As Long
    Dim wsName As String
    
    Do
        n = n + 1
        wsName = Prefix & n & Suffix
        Set ws = wb.Worksheets(wsName)
    Loop
        
WorksheetNaming:
    On Error Resume Next
    wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count)).Name = wsName
    On Error GoTo 0
    
    Exit Sub

ClearError:
    Resume WorksheetNaming
End Sub