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