Rename Excel Worksheet By Auto Increment

[Mohsin110]

Hi, I have a workbook where I have to copy a worksheet several time with the same name so please suggest me a VBA code, if I rename any worksheet and if the name already exists it should auto increment the name by "-1" and if two sheets available already it should auto increment to "-3" and so on.

 

[footoo]

Excel renames a copied sheet automatically in the format Sheet1(2), Sheet1(3), etc.
By VBA :

Sub v()
ActiveSheet.Copy After:=ActiveSheet
End Sub

 

[bebo021999]

try below code:

Option Explicit
Sub test()
Dim shname As String, sp, ws As Worksheet, max&
sp = Split(ActiveSheet.Name, "-")
For Each ws In Sheets
    If ws.Name Like sp(0) & "-*" Then
        If Split(ws.Name, "-")(1) > max Then max = Split(ws.Name, "-")(1)
    End If
Next
shname = sp(0) & "-" & max + 1
Sheets.Add
ActiveSheet.Name = shname
End Sub

 

[Mohsin110]

Excel renames a copied sheet automatically in the format Sheet1(2), Sheet1(3), etc.
By VBA :

Sub v()
ActiveSheet.Copy After:=ActiveSheet
End Sub

Yes, if you copy the sheet of same name it will automatically in the format what you mentioned, but if you copy a random sheet then try to rename and if the name already exist it will popup a message that "The name is already exist" instead it should automatically make increment by 1, so please let me know if it's possible with VBA.

 

[footoo]

You need 2 macros. One for copying sheets, and one for renaming sheets.

Assuming you will use the macro in post #2 for copying a sheet, try the following for renaming :

Sub Rename_Sheet()
Dim sh$, temp As Range, x$, tempName$
On Error Resume Next
sh = InputBox("Enter revised sheet name.")
If sh = ActiveSheet.Name Or sh = "" Then Exit Sub
Set temp = Worksheets(sh).[A1]
If temp Is Nothing Then
    ActiveSheet.Name = sh
    Exit Sub
Else
    x = 1
    If InStr(sh, " (") > 0 Then tempName = Left(sh, InStr(sh, " (") - 1)
    Do
        x = x + 1
        If ActiveSheet.Name = tempName & " (" & x & ")" Then Exit Sub
    Loop Until Evaluate("ISREF('" & tempName & " (" & x & ")'!A1)") = False
End If
If InStr(sh, " (") > 0 Then
    tempName = Left(sh, InStr(sh, " (") - 1)
    ActiveSheet.Name = tempName & " (" & x & ")"
Else: ActiveSheet.Name = sh
End If
End Sub