Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Dynamic Worksheet Tab Names.

Dynamic Worksheet Tab Names

by Allen Wyatt
(last updated August 1, 2016)

29

You probably already know that you can change the name of a worksheet tab by double-clicking on the tab and providing a new name. What if you want to do it dynamically, however? What if you want to have the value in cell A1 automatically appear as the tab name?

Unfortunately, Excel doesn't provide an intrinsic function to handle this sort of task. It is a relatively simply task to develop such a function using a macro that will do the job for you. For instance, the following macro will change the tab name to the contents of A1:

Sub myTabName()
    ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

There are several important items to note about this macro. First of all, there is no error checking. This means that if A1 contains a value that would be illegal for a tab name then the macro generates an error. Second, the macro must be manually run.

What if you want a more robust macro that does check for errors and runs automatically? The result is a bit longer, but still not overly complex:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Set Target = Range("A1")
    If Target = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Left(Target, 31)
    Exit Sub
Badname:
    MsgBox "Please revise the entry in A1." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
    Range("A1").Activate
End Sub

To set up this macro, follow these steps:

  1. Open a new workbook that has only one worksheet in it.
  2. Right-click the worksheet tab and select View Code from the resulting Context menu. Excel displays the VBA Editor.
  3. Paste (or type) the above macro into the code window.
  4. Close the VBA Editor.
  5. Locate the XLStart folder on your system. (Use the Windows search capabilities to locate the folder.)
  6. Save the workbook as an Excel macro-enabled template using the name Book.xltm in the XLStart directory. This causes the template to become your pattern for any new workbook you create.
  7. Again save the workbook as a macro-enabled template in the same directory, this time using the name Sheet.xltm. This causes the template to become the pattern for any new worksheets you insert in a workbook.
  8. Close and restart Excel.

Now, anytime you change the value in cell A1, the worksheet tab also updates.

There is one caveat to using this tip: If the value in cell A1 is a date and you want the worksheet tab to contain that date, then you may not get what you expect. The reason is simple: Excel stores dates internally as serial numbers, and that is what gets assigned to the worksheet tab, not a formatted date. If you are working with dates, then you'll need to change what actually is assigned to the tab name:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Set Target = Range("A1")
    If Target = "" Then Exit Sub
    On Error GoTo Badname
    ActiveSheet.Name = Format(Target, "mmm-dd-yy")
    Exit Sub
Badname:
    MsgBox "Please revise the entry in A1." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
    Range("A1").Activate
End Sub

Note that the only change here is what is assigned to the worksheet's Name property—it is a formatted date. You can, if you prefer, modify the date format used in the macro. You should not, however, choose a format that uses slashes because those are illegal in worksheet names.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7993) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Dynamic Worksheet Tab Names.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Random OLE Bookmarks

Word has an irritating habit of placing unwanted bookmarks in a document that you've been editing for a while. These ...

Discover More

Finding Odd Values Greater Than 50

If you have a special need to find cell values that meet two different criteria, where to start can be daunting. This tip can ...

Discover More

Protecting a Workbook from Opening in Other Programs

When working with sensitive information, you always need to be concerned that your data doesn't fall into unwanted hands. ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Changing the Color of Worksheet Gridlines

Want the gridlines in your worksheet to be a different color? You aren't limited to stodgy black; Excel lets you make them ...

Discover More

Unhiding Multiple Worksheets

You can hide a bunch of worksheets at the same time, but Excel makes it impossible to unhide a bunch at once. You can, ...

Discover More

Using Very Long Worksheet Tab Names

Excel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides some ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

2017-01-13 02:10:42

Harry

The second Macro only seems to work for the first worksheet. Changing cell "A1" in other worksheets (within the same workbook) doesn't change the worksheet name.


2016-12-23 13:59:04

pat

I've tried all the macros listed and get any of them to work. I am on Win7, Excel 2013.
I have a spreadsheet I set-up to track my hours. I would like to automatically rename the tab based on a cell in the worksheet which is the end of the pay period.
I tried the code
Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub
Changing A1 to F1 where my date is located and get an error message Run-time error 1004: you typed an invalid name for a sheet or chart. Each macro gave me a different error message.
This ought to be simple.
pat


2016-12-21 12:18:28

Matthew

Hello,

I need help here as well. I will assume what you all have discussed in these commits will satisfy what I need.

I am in need of a template which I can create that will consist of multiply tabs.
(Each of the tabs will be for an individual client)

If what I am trying to understand in the comments above, I should be able to create with the Macros above?


I will have one master list which list all of my clients and I would like to be able to click on a client on this master list and it take me directly to this page in the Excel work book. The number of clients will be over 100 which is why I am asking if I can make the excel work book easy access to an individual page.

I am reading that I will need to go to my XL start up. However, I am having trouble location this as well.


Please if anyone can help provide more assistance it will be greatly appreciated.

Thank you all in advance for your help


2016-10-17 15:30:05

RALPH P. MANFREDO

This is a great tool, unfortunately, it does not work for MS Excel Ver. 15, which is MS Excel 16 for the Mac. Any idea why? Is there a document that defines the differences in macros between Mac and Windows version of Excel?


2016-10-16 10:06:59

Joanne

Macro works great but I have one issue. I need to use it in one template so that each new sheet created will automatically behave the same - but not in any other workbook. Just workbooks based off of this template. How can I tweak this macro to work for me in this way?


2016-09-07 12:12:34

Mckenzie

Is there a way in Excel 2016 to get a range of cells to populate a new tab and name? Example I am needing b21-B54 to create a tab for each name I type in the cell.


2016-08-04 09:21:58

Jeaux

Is there a macro like this that will update all the sheet names based on cell A1- avoiding the need to run the macro several times if you have many sheets?
Thanks.


2016-08-02 13:36:59

Willy Vanhaelen

@Jane

In the first macro replace:

ActiveSheet.Name = ActiveSheet.Range("A1")

with:

ActiveSheet.Name = ActiveSheet.Range("A1") & " - " & ActiveSheet.Range("A2")

Replace the hyphen with whatever you want as separator.


2016-08-02 08:43:46

Jane

Thank you for this tip.
Is there a way that the tab can concatenate two adjacent cells for the name?
Thanks in advance.


2016-08-02 08:16:09

Ralph P. Manfredo

Hi Dorothee,

Nope. No one has even answered my question. I now have Office 2016 for the Mac and I still can't get the macro to run. There needs to be a document somewhere that identifies what VBA commands that work in Windows vs. what works in OS X.

Ralph


2016-08-01 10:58:31

Julia Miller

Update - I notice the file saved to my personal folder as I am not an admin on my computer, it would not allow me to save the template to the XLSTART folder. Question - how do I change the code if I don't want this format for EVERY workbook I create?


2016-08-01 10:47:10

Julia Miller

I followed the steps to insert this macro using the one for dates, and I initially saw the tab name change after I copy/pasted the macro. I then saved it as the book and sheet, closed Excel and then opened Excel, but the tab name is not changing. Not sure how to trouble shoot from here. Would appreciate any guidance. Also, was wondering if there is a way to use a calendar for cell A1, as we use work days, omitting holidays and weekends. This would remove the manual requirement of entering dates into each sheet. Thank you!


2016-02-18 02:44:42

RAJEESH

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "D4" Then Me.Name = "INSPECTION - " & Target.Value
End Sub

using this code i can change one of my sheet tab name excel

i need to change other page also same
target value is same but Prefix different.

Thanks


2015-04-24 10:52:56

Adile

Hello,

I used the obove mentioned VBA code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
Exit Sub
Badname:
MsgBox "Please revise the entry in A1." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
End Sub

and i just changed the Cel numbers.
it works thanks for that.

but when i use it i first need to click on another cel it does not change instantly. is there something in VBA to add so it wil refresh instantly?

thanx in advance


2015-03-19 08:41:22

Dorothee

Hi Ralph P. Manfredo. Did you find a way of doing this? I have a Mac too and having same problem


2015-03-18 07:07:39

Sergio

As always some info is omitted since it doesn't work. Only the first sheet is renamed, the second is selected, but not renamed.


2015-02-28 08:47:28

Willy Vanhaelen

If you read the tip you can find the answer in the second paragraph: It can't be done without the use of a macro.


2015-02-27 21:22:35

Frank

Hello, is there any way to do this without using a macro? I'm VBA and Macro illiterate.


2015-02-15 18:00:35

Pauline Yeo

Hi Allen
Is there a way to change a worksheet name in a formula when I make a selection from a drop down list?
Currently I am using a macro and manually edit the worksheet name and then running it.
Thanks


2015-01-30 20:04:38

rob

@ ralph

TOOLS>MACRO> VISUAL BASIC EDITOR


2014-12-30 14:18:05

MinnaKrispi

digvijay kshirsagar: This worked for me to rename tabs based on a value in each tab. I saved it as a macro and assigned it to a button on the first sheet.

Sub RenameTabs()

For i = 1 To Application.Sheets.Count
Application.Sheets(i).Select
Set Target = Range("D5")
If Target = "" Then Exit Sub
Application.Sheets(i).Name = Target.Text
Next
Application.Sheets(1).Select
End Sub


2014-09-14 01:11:03

Dinesh

If i use below macro it works fine

Sub myTabName()
ActiveSheet.Name = ActiveSheet.Range("A1")
End Sub

but if I have data in A2, A3. Code to change for multiple tabs.

Example:
A1 as 1
A2 as 2

Now, I want sheet1 as 1 and sheet 2 as 2. How to write coding.


2014-08-25 03:30:10

digvijay kshirsagar

Need help to know the macro to change multiple tab names.


2014-08-22 09:05:15

GMAN

Thank you for a quick and easy answer to my dilemma


2014-06-09 22:20:24

Ellen Fitz

Same question/comment as Ralph. How do we do this on a Mac?


2014-05-06 07:25:33

Barry

@Buzz

The line of code "Range("A1").Activate" is causing the macro to fire a second time, and also will not allow the User to leave cell A1 without entering a valid tab name. This may or may not be desirable (another outcome could be to just leave the tab name unchanged).

So one solution would be to just delete this line of code, or if you do want to be returned to cell A1 to precede the disabling of events command and immediately after re-enabling the events (see the code below).

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Format(Target, "mmm-dd-yy")
Exit Sub
Badname:
Application.EnableEvents = False
Range("A1").Activate
Application.EnableEvents = True
MsgBox "Please revise the entry in A1." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
End Sub

The way the macro is written is that the tab name is re-named every time the selection changes even if not involving cell A1. This doesn't do any harm in practice but could be considered unnecessary execution of code.

It should be noted also that if cell A1 contains a formula that if the result of the formula changes then the tab will only be updated with the change if the selection changes or if a macro changes the cell contents without there being a selection change then the tab will not be updated. If this is a problem then using the Worksheet Calculate event would be a better solution.


2014-05-05 08:22:41

Buzz

This works fine, but is there a way to get rid off the twice appearance of the message box? When I add an illegal character in A1, then the message box appears. Now, when I click "OK" the macro activates the cell A1 again (with the command Range("A1").Activate) and at the same time the message box appears again. No matter if you remove the A1 cell activation command from the macro and select the cell manually, the box appears though again. So I have to click OK twice to get rid off the message box. I am using Excel 2010.


2014-03-25 06:31:56

Bryan

Instead of ActiveSheet.Name = Left(Target, 31), use Target.Text. Then you will get whatever text shows in the cell and you won't have to make a separate macro just for use with dates.

Ralph: a brief internet search leads me to believe that you have to enable the developer tab and go at it that way.


2014-03-24 18:43:52

Ralph P. Manfredo

I am using Excel 2011 for Mac, and when I right click on the tab, I do not see the "View Code" msg and the VBA Editor does not come up. Can I still use this macro on a Mac?


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.