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.

Resources