Forum Discussion

Brass Contributor

Sep 03, 2020

Solved

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

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

Raj_123

Brass Contributor

Sep 03, 2020

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.

HansVogelaar

MVP

Sep 03, 2020

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

Raj_123
Brass Contributor
Sep 03, 2020
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
- HansVogelaar
  MVP
  Sep 03, 2020
  Raj_123
  
  Have you tried the second macro from my previous reply?
  - 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