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
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
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

Resources