Skip to main content

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

Renaming a single worksheet in Excel is typically done by right-clicking on the tab of the sheet and choosing "Rename" from the context menu. However, if you need to rename multiple worksheets using values from specified cells, or want to dynamically name a worksheet based on a specific cell's value, there are several methods you can use. This article will guide you through these techniques, making the process straightforward and efficient.

Dynamically rename a sheet from cell value with VBA

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

Step 1: Right click on the sheet name of the worksheet 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

Tip: This VBA dynamically renames the worksheet based on the value in cell A1, you can change "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:

  • If the specified cell is blank, the worksheet won’t be renamed.
  • The worksheet will be renamed each time you change the value in the specified cell.
  • If you type special characters into the specified cell, such as *, an error warning will pop up.

Rename multiple sheets based on cell values in corresponding sheets

The method described above allows for renaming one sheet at a time. However, by using the Rename Multiple Worksheets feature of Kutools for Excel, you can swiftly rename all or multiple sheets based on the value of a specified cell in each corresponding sheet. For instance, you can rename each sheet to match the value found in cell A1 of that particular sheet.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

  1. Click Kutools Plus > Worksheet > Rename Worksheets. See screenshot:
  2. In the opening Rename Multiple Worksheets dialog box, please do as follows:
    1. In the Worksheets list, check the worksheets you will rename.
    2. In the Rename Options section, 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 rename corresponding sheet with.
    4. Click the Ok button.

And now all checked sheets are renamed based on the specified cell of each sheet. See screenshot:

Note: Want to access the Rename Multiple Worksheets feature? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!

Rename multiple sheets from the specified list

Kutools for Excel’s Rename Multiple Worksheets feature also supports to rename multiple sheets based on cell values in a specified list.

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

  1. Click Kutools Plus > Worksheet > Rename Worksheets.
  2. In the opening Rename Multiple Worksheets dialog box, configure as follows:
    1. In the Worksheets list, select the worksheets you will rename.
    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, and click the  button to open the second Rename Multiple Worksheets dialog box (see the right screenshot). Then, select the list of cells that you will rename by their values, and click the OK button.
    4. Click the Ok button to apply the renaming.
    5.

Then you will see all checked worksheets' names are replaced with the cell values in the specified list. See below screenshot:

Note: Want to access the Rename Multiple Worksheets feature? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!

Demo: name sheets based on cell values (from list) in Excel


Kutools for Excel: Over 300 handy tools at your fingertips! Start your 30-day free trial with no feature limitations today. Download Now!

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (7)
No ratings yet. Be the first to rate!
Lorenzo
Lorenzo
This comment was minimized by the moderator on the site
Hi there, one question if i want to use the value of 2 cells (A1 & B1) what i have to change?
Reply
0
0
Amanda Lee
Amanda Lee     Lorenzo
This comment was minimized by the moderator on the site
Hi there,

Please try the code below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim sheetName As String
    
    If Not Intersect(Target, Me.Range("A1:B1")) Is Nothing Then
        sheetName = VBA.Left(Me.Range("A1").Value & Me.Range("B1").Value, 31)
        
        If sheetName <> "" Then
            Application.ActiveSheet.Name = sheetName
        End If
    End If
End Sub

Once you done pasting the code to the View Code window, please select the cell A1 or B1 to make the code run.

Amanda
Reply
Report
0
0
Олег
Олег
This comment was minimized by the moderator on the site
Здравствуйте! Подскажите, пожалуйста, а как в Excel создать левую панель, в которой разместить названия листов? То есть перенести ярлыки листов влево (сейчас то они снизу)
Reply
0
0
Amanda Lee
Amanda Lee     Олег
This comment was minimized by the moderator on the site
Hi there,

Hope this tutorial would help you: https://www.extendoffice.com/documents/excel/3117-excel-worksheet-tabs-in-a-vertical-arrangement-on-left-side.html

Amanda
Reply
Report
0
0
sriggins
sriggins
This comment was minimized by the moderator on the site
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
Reply
Report
0
0
HHani
HHani
This comment was minimized by the moderator on the site
This is awesome, thank you so much....
Reply
0
0
Mel
Mel
This comment was minimized by the moderator on the site
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??
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations