SOLVED

VBA to rename sheets

Brass Contributor
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
7 Replies
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
best response confirmed by Raj_123 (Brass Contributor)
Solution

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

That's not the way Excel works.

@Hans Vogelaar,

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.

1 best response

Accepted Solutions
best response confirmed by Raj_123 (Brass Contributor)
Solution

@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

View solution in original post