How to rename the multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2324578%22%20slang%3D%22en-US%22%3EHow%20to%20rename%20the%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2324578%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20sales%20report%20content%20with%20multiples%20sheets%20(around%2060%20sheets)%2C%20and%20each%20sheets%20are%20referring%20to%20a%20shop%2C%20and%20the%20sheet%20auto%20named%20with%20the%20numeric%20number%20(eg.%20001%2C%20002%2C%20003%20)%20after%20generated%20from%20the%20system.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActually%2C%20each%20sheet%20content%20the%20respective%20shop%20names%20under%20cell%20B5%2C%20sales%20man%20and%20the%20sales%20amount%2C%20is%20there%20any%20fast%20way%20to%20rename%20the%20individual%20tabs%20(aka%20sheets%20)%20based%20on%20the%20cell%20B5%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Nicole_Lim1003_2-1620187421846.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F278261i08650602908E38C8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Nicole_Lim1003_2-1620187421846.png%22%20alt%3D%22Nicole_Lim1003_2-1620187421846.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2324578%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2324770%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20rename%20the%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2324770%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20needs%20VBA%20Macro%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20RenameSheets()%0AFor%20i%20%3D%201%20To%20Sheets.Count%0AIf%20Worksheets(i).Range(%22B5%22).Value%20%26lt%3B%26gt%3B%20%22%22%20Then%0ASheets(i).Name%20%3D%20Worksheets(i).Range(%22B5%22).Value%0AEnd%20If%0ANext%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20it%20works%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EPress%26nbsp%3B%3CSTRONG%3EAlt%20%2B%20F11%3C%2FSTRONG%3E%26nbsp%3Bto%20open%20VBA%20editor%20window.%3C%2FLI%3E%3CLI%3EHit%26nbsp%3B%3CSTRONG%3EInsert%3C%2FSTRONG%3E%26nbsp%3Bthen%26nbsp%3B%3CSTRONG%3EModule%3C%2FSTRONG%3E%2C%20then%20copy%20%26amp%3B%20paste%20this%20VBA%26nbsp%3B%20code.%3C%2FLI%3E%3CLI%3ESave%20the%20WB%20as%20Macro%20Enabled%20*.xlsm.%3C%2FLI%3E%3CLI%3EReturn%20to%20Sheet%20%26amp%3B%20RUN%20the%20Macro.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSTRONG%3EN.B.%20%3C%2FSTRONG%3E%3CEM%3EThis%20will%20work%20only%2C%20if%20each%20sheet%20has%20name%20in%20cell%20B5.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

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?

 

Nicole_Lim1003_2-1620187421846.png

 

 

 

1 Reply

 

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.