Forum Discussion

Raj_123's avatar
Raj_123
Brass Contributor
Sep 03, 2020
Solved

Renaming the only the sheet

There are many sheet tabs in excel for say sheet1 sheet2 sheet3 sheet 4, sheet5 and so on...however i have deleted sheet2 ..the remaining will be sheet1, sheet3, sheet4,sheet5 and so on..

After renaming it should look like Rob1,Rob3,Rob4,Rob5 and so on... But the VBA code that i have is giving the answer as Rob1, Rob2,Rob3,Rob4 .
I don't want the serial number to get disturbed

I want a VBA code for this please.
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Sep 04, 2020

    Raj_123 

    Like this:

    Sub RenameSheets()
    Dim OldName As String
    Dim NewName As String
    Dim wsh As Worksheet
    Dim p As Long
    NewName = InputBox("Enter the new name")
    If NewName = "" Then
        Beep
        Exit Sub
    End If
    For Each wsh In Worksheets
        OldName = wsh.Name
        For p = Len(OldName) To 1 Step -1
            If Not IsNumeric(Mid(OldName, p, 1)) Then
                Exit For
            End If
        Next p
        wsh.Name = NewName & Mid(OldName, p + 1)
    Next wsh
End Sub

10 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 03, 2020

    Raj_123 

    For example:

    Sub RenameSheets()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
        wsh.Name = Replace(wsh.Name, "Sheet", "Rob")
    Next wsh
End Sub
    • Raj_123's avatar
      Raj_123
      Brass Contributor
      Sep 03, 2020
      It actaully worked thankful to you...I have taken Rob as example but i want macro to ask me for a new name everytime after i run the macro...if you give me this it is a perfect one from you... awaiting for your reply.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 03, 2020

        Raj_123 

        Will the sheets be named Sheet1 etc. when you run the macro? If so:

         

         

        Sub RenameSheets()
    Dim NewName As String
    Dim wsh As Worksheet
    NewName = InputBox("Enter the new name")
    If NewName = "" Then
        Beep
        Exit Sub
    End If
    For Each wsh In Worksheets
        wsh.Name = Replace(wsh.Name, "Sheet", NewName)
    Next wsh
End Sub

         

         

        If the sheets have already been renamed, we can prompt for the old and new names:

         

         

        Sub RenameSheets()
    Dim OldName As String
    Dim NewName As String
    Dim wsh As Worksheet
    OldName = InputBox("Enter the old name, for example Sheet")
    If OldName = "" Then
        Beep
        Exit Sub
    End If
    NewName = InputBox("Enter the new name, for example Rob")
    If NewName = "" Then
        Beep
        Exit Sub
    End If
    For Each wsh In Worksheets
        wsh.Name = Replace(wsh.Name, OldName, NewName)
    Next wsh
End Sub

         

Resources