Rename Excel Worksheets, Add Variable if worksheet name already exists

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

Hi jessello,

try this:

Please Login or Register  to view this content.

A minor adjustment, if you want the second sheet to be named "Jane, Mar (2)" instead of "Jane, Mar (1)" like in my previous post, first define i=1

Please Login or Register  to view this content.

Thanks Berlan!!
I made minor adjustments to the code (because there's a "summary" worksheet and I don't want the macro to rename that) and it works beautifully

Given that ws is set as the "summary" worksheet and that ws.name corresponds to this, the sheet should not be renamed

Glad it works for you, and I appreciate the rep you gave me.

All best