Forum Discussion

Raj_123's avatar
Raj_123
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'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 03, 2020

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
  • Raj_123's avatar
    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's avatar
      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's avatar
        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

Resources