Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to name sheets based on cell values (from list) in Excel?

Normally, we can easily rename a worksheet with right clicking the sheet in the Sheet Tab and selecting Rename from right-clicking menu in Excel. What if renaming all/multiple worksheets with specified cell values? And what if dynamically naming a worksheet based on a specified cell value? In this article, a couple of methods will help you solve them easily.

Dynamically name a sheet based on cell value with VBA

Name multiple sheets based on cell values in corresponding sheets

Name multiple sheets from the specified list

Rename multiple sheets based on cell values in corresponding sheets in Excel

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial


We can dynamically name a worksheet from a certain cell’s value with VBA in Excel, and you can do as follows:

Step 1: Right click the worksheet in the Sheet Tab that you will dynamically name it by a cell value, and select View Code from the right-clicking menu.

Step 2: In the opening Microsoft Visual Basic for Applications window, paste the following VBA code into the module window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

Note: This VBA can dynamically rename the worksheet based on the value of Cell A1, and you can change the A1 to other cells based on your needs.

Step 3: Save the VBA code and close the Microsoft Visual Basic for Application window.

Then you will see the sheet name is dynamically changed based on the specified cell value.

Notes:

  1. If the specified cell is blank, the worksheet won’t be renamed;
  2. If you type special characters into the specified cell, such as *, it will pop up an error warning.

With Kutools for Excel's Rename Multiple Worksheets feature, you can quickly name all/multiple sheets with the specified cell in corresponding sheet. For example, rename each sheet with the Cell A1 value in each sheet.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click Enterprise > Worksheet > Rename Multiple Worksheets. See screenshot:

2. In the opening Rename Multiple Worksheets dialog box, please (see screenshot below):

(1) In the Worksheets list, please check the worksheet whose name you will change;

(2) In the Rename Options section, please check the Replace original sheet name option;

(3) In the New Worksheet Name section, please check the Rename worksheets with specific cell option, and specify the cell whose content you will name corresponding sheet with.

(4) Click the Ok button.

And now all checked sheets have been renamed with the specified cell of each sheet. See screenshot:

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


Kutools for Excel’s Rename Multiple Worksheets feature also supports to name multiple sheet with cell values in the specified range.

Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

1. Click the Enterprise > Worksheet > Rename Multiple Worksheets.

2. In the opening Rename Multiple Worksheets dialog box, you need to (see screenshots below):

(1) In the Worksheets list, check the worksheet you will rename with cell values.

(2) In the Rename Options section, check the Replace original sheet name option;

(3) In the New Worksheet Name section, check the From Specific range option, click the  button to open the second Rename Multiple Worksheets dialog box, select the cells that you will rename by their values, and click the OK button.

(4) Click the Ok button to apply the renaming.

Then you will see all checked worksheet names are changed to the specified cell values. See below screen shot:

Kutools for Excel - Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required! Get it now!


In this Video, the Kutools tab and the Kutools Plus tab are added by Kutools for Excel. If need it, please click here to have a 60-day free trial without limitation!



Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Sam Riggins · 8 months ago
    I used the dynamic sheet name coding and functionally it works well, but I get the Runtime Error 1004 whenever I click inside a cell. Anyone have any info on how to correct this?


    Thanks
  • To post as a guest, your comment is unpublished.
    HHani · 1 years ago
    This is awesome, thank you so much....
  • To post as a guest, your comment is unpublished.
    Mel · 1 years ago
    so I am trying to do this, but nothing is happening - my sheet names aren't changing, everything is the exact same. Do you know what I am doing wrong??