Macro that generates new worksheet with a dependent worksheet name

[afniz]

Hello guys,do you have any idea how to create a new sheet named: "Test" however if the sheet "test" already exists to create "Test 2". Going forward, if the sheet "Test 2" already exists to create "Test 3" and so on...I would be very grateful for any suggestions.Thank you

[mikerickson]

Try

Sub test()
Dim newName As String
newName = "test"
With Worksheets.Add
On Error Resume Next
Do
Err.Clear
.Name = newName
newName = NextName(newName)
Loop While (Err <> 0)
On Error GoTo 0
End With
End Sub

Function NextName(cName As String) As String
Dim nameWords As Variant
nameWords = Split(cName & " 1", " ")
NextName = nameWords(0) & " " & (Val(nameWords(1)) + 1)
End Function