Forum Discussion

Raj_123's avatar
Raj_123
Brass Contributor
Sep 03, 2020
Solved

I want to rename all the sheet tabs of an excel

Hi
If there are sheet 1, sheet 2, sheet3 , sheet4 and sheet5 and i have deleted the sheet 4 tab...now the sheets in excel would be sheet1, sheet2, sheet3 and sheet5..( please observe there would be no sheet4 as i have deleted)

I have a VBA code and used to rename (renamed as 'Raj') all the sheets at one go but the result i am getting is Raj1,Raj2,Raj3,Raj4 however, i want the result as Raj1,Raj2,Raj3,Raj5.

How to do this?
excel
  • Raj_123's avatar
    Raj_123
    Sep 04, 2020
    Thank you friend.

    I have used the above code and it actaully worked but issue arised is as per our procedures we renumber and rename the sheets using a macro button then it would look like this 1-Rob1, 2-Rob2,...10-Rob10,....100-Rob100 now i have inputtted the character as 5 for the old and renamed the result came out missed from10-Rob10 (bRaj10) bcoz it has 6 characters the and the 100-Rob10 has 7chartacters (obRaj100).

    I have used the below code but the lapse is it is changing the serial number but it ibrilliantly changes the sheet name just by asking new code....can we adjust the below code accordingly.



    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

10 Replies

Sort By
  • mtarler's avatar
    mtarler
    Silver Contributor
    Sep 03, 2020

    Raj_123  without the VBA code we can't tell you exactly what lines to change but in the VBA code there must be something like a loop something like For i = 1 to Activeworkbook.sheets.count

    and then an assignment like

       Activeworkbook.sheet(i)="Raj"&i

    you need to instead do something like

       temp=mid(Activeworkbook.sheet(i).name,6)

       Activeworkbook.sheet(i)="Raj"&temp

     

     

    • Raj_123's avatar
      Raj_123
      Brass Contributor
      Sep 03, 2020
      May i please have entire VBA code that you are refering to? Instead of Raj, i want macro need to ask me a name for all the time
      • mtarler's avatar
        mtarler
        Silver Contributor
        Sep 03, 2020

        Raj_123  Here is a routine that will do what you ask.  It needs to be added to a module in the VBA and will ONLY properly work to rename from the original sheet names of Sheet1, Sheet2, ... and it will maintain the original sheet numbers.  But if you rename everything Raj1, Raj2, ... and change your mind and want RAJA instead, this macro would need to be adjusted.

        Sub renameSheets()
    shtName = InputBox("Enter the new name to use for each sheet instead of 'sheet'", "New Sheet Name")
    If shtName = "" Then
        Exit Sub
    End If
    For i = 1 To ActiveWorkbook.Sheets.Count
        ActiveWorkbook.Sheets(i).Name = shtName & Mid(ActiveWorkbook.Sheets(i).Name, 6)
    Next i
End Sub

         

Resources