Adding Sheets With Sequential Names Stops Incrementing After the 10th Sheet

Question

I came across the below code when looking for ways to sequentially add sheets using VBA. This code works great and does exactly what I need it to do up until it reaches the 10th worksheet. Once it reaches the 10th sheet ( i.e., "Combined-10" ), it throws an error when attempting to advance to the 11th sheet etc. I'm fairly new to VBA and do not know how to correct this. I'm simply needing help fixing this issue so that the worksheet continues to advance to the next sequential sheet once it reaches sheet number 10. Any help would be much appreciated!

Link to original code: Adding Sheets With Sequential Names Adding Sheets With Sequential Names

Option Explicit

Sub GetAvailableSheeName()

Dim sht As Worksheet
Dim temp_sht
Dim sht_name, last_sht As String
Dim shtNumber
Dim temp_counter, loop_i, counter, num As Integer

Const Available_sht As String = "Combined-"

temp_counter = 0
For Each sht In ThisWorkbook.Worksheets

    If LCase(Left(sht.name, Len(Available_sht))) = LCase(Available_sht) Then

        shtNumber = Split(sht.name, "-")(1)

        If IsNumeric(shtNumber) Then
            If shtNumber > temp_counter Then
                temp_counter = shtNumber
                last_sht = sht.name
            End If

        Else
            sht_name = sht.name

        End If

    Else
            sht_name = sht.name
    End If

Next sht

If temp_counter = 0 Then

   ThisWorkbook.Sheets.Add(After:=Sheets(sht_name)).name = "Combined-1"
Else

   ThisWorkbook.Sheets.Add(After:=Sheets(last_sht)).name = "Combined-" & temp_counter + 1

    For loop_i = 1 To temp_counter + 1

        For Each sht In ThisWorkbook.Worksheets
             counter = 0
             If LCase("Combined-") & loop_i = LCase(sht.name) Then

               counter = 1
               Exit For
             End If

        Next sht

         If counter = 0 Then
            If loop_i = 1 Then
              ThisWorkbook.Sheets.Add(Before:=Sheets(1)).name = "Combined-" & loop_i
            Else
              num = loop_i - 1
              ThisWorkbook.Sheets.Add(After:=Sheets("Combined-" & num)).name = "Combined-" & loop_i
            End If

         End If

        Next loop_i
End If

End Sub

Answer 1

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