Forum Discussion

Anonymous's avatar
Anonymous
May 05, 2021

How to rename the multiple sheets

I have a sales report content with multiples sheets (around 60 sheets), and each sheets are referring to a shop, and the sheet auto named with the numeric number (eg. 001, 002, 003 ) after generated from the system.

 

Actually, each sheet content the respective shop names under cell B5, sales man and the sales amount, is there any fast way to rename the individual tabs (aka sheets ) based on the cell B5?

 

 

 

 

excel

1 Reply

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    May 05, 2021

     

    This needs VBA Macro:

     

    Sub RenameSheets()
For i = 1 To Sheets.Count
If Worksheets(i).Range("B5").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("B5").Value
End If
Next
End Sub

     

    How it works:

    • Press Alt + F11 to open VBA editor window.
    • Hit Insert then Module, then copy & paste this VBA  code.
    • Save the WB as Macro Enabled *.xlsm.
    • Return to Sheet & RUN the Macro.

    N.B. This will work only, if each sheet has name in cell B5.

Resources