Forum Discussion

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

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

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

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.