Renaming the only the sheet

[Raj_123]

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.

[#2]

@Raj_123 

For example:

Sub RenameSheets()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
        wsh.Name = Replace(wsh.Name, "Sheet", "Rob")
    Next wsh
End Sub

[#3]

It actaully worked thankful to you...I have taken Rob as example but i want macro to ask me for a new name everytime after i run the macro...if you give me this it is a perfect one from you... awaiting for your reply.

[#4]

@Raj_123 

Will the sheets be named Sheet1 etc. when you run the macro? If so:

Sub RenameSheets()
    Dim NewName As String
    Dim wsh As Worksheet
    NewName = InputBox("Enter the new name")
    If NewName = "" Then
        Beep
        Exit Sub
    End If
    For Each wsh In Worksheets
        wsh.Name = Replace(wsh.Name, "Sheet", NewName)
    Next wsh
End Sub

If the sheets have already been renamed, we can prompt for the old and new names:

Sub RenameSheets()
    Dim OldName As String
    Dim NewName As String
    Dim wsh As Worksheet
    OldName = InputBox("Enter the old name, for example Sheet")
    If OldName = "" Then
        Beep
        Exit Sub
    End If
    NewName = InputBox("Enter the new name, for example Rob")
    If NewName = "" Then
        Beep
        Exit Sub
    End If
    For Each wsh In Worksheets
        wsh.Name = Replace(wsh.Name, OldName, NewName)
    Next wsh
End Sub

[#5]

Will the sheets be named Sheet1 etc. when you run the macro? If so

Hans vogelaar, thanks for your reply. I have tried for the above and it worked

But a small lapse is that it is allowing me to rename just only once...though it is asking me to change after i rerun the macro but the name not getting changed for the second time

Please help me friend...

Say, from sheet i have changed to Rob. I have to rename it to Sandy now if i rerun this

[#6]

@Raj_123 

Have you tried the second macro from my previous reply?

[#7]

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

[#8]

Waiting for your reply friend

[#9 · Accepted 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

[#10]

Home Microsoft Learn

Select the language
(English) English
Click here to refresh translation
Skip to Topic Message
New sheet tab creation issue
Raj_123
Raj_123NEW CONTRIBUTOR
7 hours ago

New sheet tab creation issue
There are 15 tabs in the excel sheet( it is an already saved excel sheet on the desktop as ABC.xlsx). Out the 15 sheet tabs, i have deleted sheet4,sheet6,sheet11and sheet 13.

Now my sheet selection is on sheet2.when i click on new sheet option(+) the new sheets created are sheet4,sheet6, sheet11 and sheet13, i want the new sheets to start from sheet16 instead it is creating all the old deleted sheets first..why?

[#11]

@Raj_123 

You might use a macro to insert a new sheet:

Sub NewSheet()
    Dim wsh As Worksheet
    Dim OldName As String
    Dim p As Long
    Dim n As Long
    Dim m As Long
    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
        n = Val(Mid(OldName, p + 1))
        If n > m Then m = n
    Next wsh
    Worksheets.Add.Name = "Sheet" & m + 1
End Sub

You can assign the macro to a custom keyboard shortcut and/or to a Quick Access Toolbar button.