VBA Add a new sheet, if name exists add a number

Question

the following code checks if sheet named "Final" exists, if yes it creates another worksheet but the name depends on the number of sheets in a workbook. So if there's only one sheet named "Final" and 10 different sheets (altogether 11 sheets), the macro will add a new sheet named "Final_12". How to amend the code so that it creates "Final_1", "Final_2" ect. sheets?

Set WSF = wb.Worksheets.Add(After:=wb.Worksheets(PayrollWS))
Set NewSht = ActiveSheet
newShtName = "Final"

'if "Final" sheet exists, there will be another added, e.g. "Final_2"
For Each Sht In wb.Sheets
    If Sht.Name = "Final" Then
        newShtName = "Final" & "_" & wb.Sheets.Count  'how to amend this part?
    End If
Next Sht

NewSht.Name = newShtName

Answer 1

You'll need to count the number of times Final exists as a sheet name.

'if "Final" sheet exists, there will be another added, e.g. "Final_2"
Dim cnt as Long
For Each Sht In wb.Sheets
    If Left$(Sht.Name,5) = newShtName Then cnt = cnt + 1
Next Sht

NewSht.Name = newShtName & IIF(cnt>0, "_" & cnt, "")

Answer 2

This code will do the job, it consist of one sub and one function

Sub NewFinalSheet()
Dim wsNew As Worksheet
If Not wsExits("Final") Then
    Set wsNew = thisworkbook.Worksheets.Add
    wsNew.Name = "Final"
    Exit Sub
End If

Dim i As Long, newWsName As String
Do
    i = i + 1
    newWsName = "Final_" & i
    If Not wsExits(newWsName) Then
        Set wsNew = ThisWorkbook.Sheets.Add
        wsNew.Name = newWsName
        Exit Sub
    End If
Loop
End Sub

The function part you can pick one of this two: This is simpler but slower:

Function wsExits(shName As String) As Boolean
Dim ws As Worksheet

For Each ws In thisworkbook.Worksheets
    If ws.Name = shName Then
        wsExits = True
        Exit Function
    End If
Next ws

wsExits = False
End Function

This should be faster but is error focus

Function wsExits(shName As String) As Boolean
On Error GoTo NotE
If ThisWorkbook.Worksheets(shName).Name = shName Then
    wsExits = True
    Exit Function
End If

NotE:
    wsExits = False
End Function