Forum Discussion

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

VBA to rename sheets

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 and Rob in the above name is taken just as an example i want a new name should be asked by macro when ever i run it.

Please help me
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

7 Replies

    • Raj_123's avatar
      Raj_123
      Brass Contributor
      Sep 03, 2020
      Yes i have tried the second one having the option of old and new...it is creating an issue...for say, the old sheet name now is ROB1 i have to type just Rob when ever it promted for old...if i type ROB1 everything will go for a toss...i verymuch underatood what to keyin...but it will not be a user-friendly to the people who does not know VBA they just...you have helped me a lot.

      I have used the below VBA code
      i can change the names n number if times " however it is changing the sheet numbers" that is all my concern. After deleting the sheet4 and i rename the answer to be is Rob1, Rob2,Rob4,Rob5....but it is showing the result as Rob1,Rob2,Rob3,Rob4...

      I trust you can modify the and you are super talented

      Sub ChangeWorkSheetName()

      Dim Rng As Range

      Dim WorkRng As Range

      On Error Resume Next

      newName = Application.InputBox(“Name”, xTitleId, “”, Type:=2)

      For i = 1 To Application.Sheets.Count

      Application.Sheets(i).Name = newName & i

      Next

      End Sub
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        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

Resources