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,sh...
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
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
    • Raj_123's avatar
      Raj_123
      Brass Contributor
      Sep 04, 2020
      HansVogelaar,

      The VBA code which you have given is working fine...the errors coming are because of the combinations of other VBA codes...

      Kudos to you..thanks for your brilliant support. Happy to have in this community.

    • Raj_123's avatar
      Raj_123
      Brass Contributor
      Sep 04, 2020
      Here comes the issue again!!!

      I have deleted the sheet2,sheet4 and sheet6 among the 20 shets i have ahand renamed the sheets to Rob with the above code i got the answer as Rob1,Rob3,Rob5,Rob7 and so on...

      Now, i have added the 10 new sheets the sheets are automatically started from sheet1,Sheet2, Sheet 3, sheet4, and so on...to sheet10

      Actually the newly added sheets should start from sheet21 and end at sheet30. bcoz of this if i rename the sheets again from Rob to Martin there is a error occuring to rename sheets because of clash between Rob1, Rob3 and soon as it is getting conflicted with sheet1to sheet10 which are newly created...feeling sad

      Please help me
    • Raj_123's avatar
      Raj_123
      Brass Contributor
      Sep 04, 2020
      Hello friend,

      It is working now...will let you know if i find any difficulties....i am actaully trying now with around 250 pages adding the pages in between and then renaming got an vba 404 error but i have closed the worksheet and reopened it and worked for now....not able to trace why i got an error intially.

Resources