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!!
Bookmarks