Hi everyone!

I know there are a few threads regarding this issue but I can't seem to get any to work in my excel file. Currently, the macro renames worksheet based on last name "F16" and first 3 letters of first name "B16". When running this macro, I would like it to add a number to the worksheet if the worksheet name already exists. Example, if there are three "Mary Jane"s, one worksheet would be "Jane, Mar" and the other would be "Jane, Mar (2)" and "Jane, Mar (3)"

Sub RenameSheet()

Dim rs As Worksheet

On Error GoTo ErrorHandler

For Each rs In Sheets
If rs.Name <> "Summary" Then
rs.Name = rs.Range("F16") & ", " & Left$(rs.Range("B16").Value, 3)
End If
Next rs
Exit Sub

ErrorHandler:
rs.Name = rs.Range("F16") & ", " & Left$(rs.Range("B16").Value, 3) & " (2)"
Resume Next


End Sub


Any help and insight would be appreciated!!