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

Dynamic Worksheet Tab Names

by Allen Wyatt
(last updated January 4, 2014)

104

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 (such as nothing at all or more than 31 characters), 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 template using the name BOOK.XLT 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 template in the same directory, this time using the name SHEET.XLT. 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.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2145) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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

Changing Sort Order

When sorting information, Word follows some pretty strict rules. If you want to modify how those rules are applied, you may ...

Discover More

Eliminating "Before Spacing" at the Top of a Page

When formatting paragraphs in Word, you have several options to adjust the spacing before, within, and at the end of each ...

Discover More

Ensuring Rows and Columns are Empty

Before you go about deleting rows and columns helter-skelter, it is a good idea to determine if there is anything in the row ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Freezing Worksheet Tabs

If you have a lot of worksheets in a workbook, you may wonder if you can "freeze" the position of some of those worksheet ...

Discover More

Sheets for Days

Need a quick way to have a worksheet for each day in a month? Here's a macro that makes the worksheet creation a snap.

Discover More

Turning Off Worksheet Tabs

Look at the bottom of a worksheet and chances are you will see tabs for all the worksheets in the current workbook. Want to ...

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. 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 2 + 1?

2017-05-24 03:54:22

Michael (Micky) Avidan

@Paul Dowgill,
I suggest to rewrite the IF statement to something like:
If Target = "" Or ActiveSheet.Name = Left(Target, 31) Then Exit Sub
in order to eliminate the running of the rest of the command in case the Sheet's name already matches the A1 value.
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2017)
ISRAEL


2017-05-24 03:49:47

Michael (Micky) Avidan

@Paul Dowgill,
You presented the Macro which changes the sheet's name according to the value in cell A1.
To my opinion, it has nothing to do with the Macro which re-directs back to the HOME sheet - which produces the error.
Please upload your entire Workbook to any File Hosting site and let us have a link to dowload it.
*** If your WB contains private data - delete/change it ***
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2017)
ISRAEL


2017-05-23 09:51:48

Paul Dowgill

Hi,

This Macro has proved extremely useful however i'm having trouble with it.

I have a "Home" page which has links to all the tabs in the workbook. I've figured out how to ensure these work when the tab names change. Each tab has a link to the "Home" page.

The problem occurs when i am on one of the tabs, and click on a hyperlink to take me to the "Home" page. I receive an error message. If i navigate to the home page using the tabs then i don't get the same message, and if i select the cell with the link before pressing it then i don't get the message either.

Any ideas?

The formula i am using is
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


2017-03-10 10:45:37

Brian

Nevermind! Silly mistake on my end. Thanks again.


2017-03-10 10:43:13

Brian

Hello,
Your tab name code worked great. Thank you.
I have many hyperlinks in the worksheets, and all of them "broke" when i added the code.
When I hover over a hyperlinked cell, I see the tab name change code along with the hyperlink destination.
Perhaps i need to bracket, or partition the code so that it does not modify other stuff in the worksheet?
(This is my very first VBA code exercise... thank you for your help).


2017-02-03 10:34:43

Willy Vanhaelen

@Carla

Enter the date in A1 and format it as "dddd, mmmm d, yyyy" without quotes. Then run this macro:

Sub myTabName()
ActiveSheet.Name = Format(ActiveSheet.Range("A1"),"mmm d")
End Sub


2017-02-02 22:20:59

Carla

Hi! I think you are the guy to help me on my macro! I would love to have A1 be a shortened tab name. I knew there is an easier way than to type it-I am trying to work smart here.
What I want:
A1 = Thursday, February 2, 2017
tab name= Feb 2

And I would like it to repeat for the entire month.

IS that a possiblity? Would love to get your help with this!


2017-01-12 05:49:13

Alan Elston

Hi draega,
To change the range to reference to a cell in another worksheet is possible such as by this:
Set Target = Worksheets("Sheet2").Range("A1")

I am sorry but the rest of your code and question does not make any sense to me.
The code you posted does not work at all for me
You appear to have a Named Range, such as “Other” in place of “A1”. That is OK. _..
Other.Name
_.. makes no sense to me. I do not understand how such a code line would work, or what it is intended to do. That code line errors by me.
Alan


2017-01-11 23:15:57

draega

in the
Set Target = Range("A1")
part, can the range be a reference to a cell in another worksheet?
I modified this code to include naming of several worksheets, but it seems like I've hit a limit of 7 tabs that can be named like this, does that sound right?? Otherwise I have some other issue. Here is how I modified the code (below), which works great for up to 7 tabs, then for the 8th one it keeps giving me an error message no matter what name I try to type in. Any ideas?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Set Target = Range("Other")
If Target = "" Then Exit Sub
On Error GoTo Badname_Other
Other.Name = Left(Target, 31)

Set Target = Range("Uncommon")
If Target = "" Then Exit Sub
On Error GoTo Badname_Uncommon
Uncommon.Name = Left(Target, 31)

Exit Sub

Badname_Other:
MsgBox "Please revise the tab name entry in named range 'Other'." & Chr(13) _
& "Duplicate entry or too many characters." & Chr(13)

Exit Sub

Badname_Uncommon:
MsgBox "Please revise the tab name entry in named range 'Uncommon'." & Chr(13) _
& "Duplicate entry or too many characters." & Chr(13)

End Sub



2016-12-31 09:21:50

Alan Elston

I think it is difficult to get the exact info. across here that I need to fully understand the problem.
However, maybe this will help get you started.
In Column A in the first Tab Worksheet of the Workbook in which you have the following code in you have your Tab Names listed, like_..
January 7
January 21
_.. etc.. etc.
The code will Add a Worksheet for each of the dates from that list , and give the Worksheet Tab Name that date.
Alan

Sub AddNamedWorksheetsWonk() 'http://excel.tips.net/T002145_Dynamic_Worksheet_Tab_Names.html
Dim Ws1 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets.Item(1) ' Alternative: Set Ws1 = ThisWorkbook.Worksheets.Item("Tab Names")
Dim arrNames() As Variant: Let arrNames() = Ws1.Range("A1:A" & Ws1.Cells(Ws1.Rows.Count, 1).End(xlUp).Row & "").Value
' Alternative: Let arrNames() = Application.Range("='[" & ThisWorkbook.Name & "]" & Ws1.Name & "'!A1:A" & Ws1.Cells(Ws1.Rows.Count, 1).End(xlUp).Row & "").Value
Dim SteerVariment As Variant
For Each SteerVariment In arrNames()
Let ThisWorkbook.Worksheets.Add.Name = SteerVariment
Next SteerVariment
End Sub


2016-12-31 08:30:44

Barry

@Matthew,

Yes, what you want is easily done.

On the codepage for the worksheet with the master list of Client names use the "Worksheet_Change" event to create a copy of the template worksheet, name it and create the hyperlink to it. It obviously only needs to operate on one cell at a time rather than the whole list.

You'll need to cope this the situation if someone modifies an existing Client (e.g if at a later date a spelling mistake is corrected.That is Change the Tab Name and hyperlink.


2016-12-30 09:55:13

Nick

@ Alan

Thanks Alan, I think I should elaborate, my bad.

I'm hoping to store all of the tab names in a sheet, let's call that sheet "Tab Names". I currently have sheets for each two week pay period in a year, and the name for each sheet is the pay day (ex: January 7). In the "Tab Names" sheet, I'll have every pay day listed for a given year (ex, January 7, January 21, etc.). I'd like to run the macro to change all the sheets names to the pay day in the "Tab Names" sheet. This will make it easier every year (as pay days change) to rename the sheets.

Thanks!


2016-12-30 08:17:00

Matthew

@Barry

Hello,

Sorry for my late response. I ended up doing just as you suggested Barry. After I created each individual tab and renamed each tab to the clients name. I went on my Master and each name in Column A 1 I inserted a Hyperlink using place in this document option.

Since this would be used by others in the office as well my main question would be.

Is there a way to make it become Automated when another person would add a client to the master document in column A 1?


Thank you very much again for your help Barry


2016-12-30 05:23:40

Alan Elston

@ Nick
Do you mean something like this:
Sub TabName()
MsgBox prompt:="Name of second tab from left is """ & ThisWorkbook.Worksheets.Item(2).Name & """" & vbCrLf & "Code Name of second tab from left is """ & ThisWorkbook.Worksheets.Item(2).CodeName & """"
End Sub

Alan


2016-12-29 10:28:11

Nick

Is there a way to get the name of a tab from a different sheet that has a tab name that won't change?


2016-12-22 05:38:23

Barry

@Matthew

There is another tip on this website which I think will do virtually all that you want with just a couple of minor changes. You'll probably find what you are looking for there.

http://excelribbon.tips.net/T013463_Creating_Worksheets_from_a_List_of_Names.html

To copy a template worksheet substitute "Worksheets("TemplateName").Copy" for "Worksheets.Add" (put the actual template sheet name for "TemplateName").

I would then create a hyperlink on the list of Clients which when clicked would take you straight to the Clients worksheet.
The code:
ActiveSheet.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= "Sheet3!A1", _
TextToDisplay:=Activesheet.Name
will create the hyperlink and would need to be put in the loop to do this job.

There are some caveats here if you have Clients with the same name e.g. two John Smith's for instance. Excel requires worksheets to have unique names. The code will error out as it stands which is not the most elegant way of handling this.


2016-12-21 12:12:13

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 a 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-12-13 16:13:21

draega

I know this is a really old post, but I used this code and it worked great. However, after I protected my workbook (and nothing else changed), I keep getting the error as if I had renamed a tab with too many characters.

What gives?!


2016-09-07 05:17:26

Barry

@Michael

There's is another tip on this website that addresses this issue specifically.

You'll probably find what you are looking for there.

http://excelribbon.tips.net/T013463_Creating_Worksheets_from_a_List_of_Names.html


2016-09-06 05:50:13

Michael

I am trying to figure out if this is possible.

Say in Excel, in Column D, I have a list of 26 teachers names (D1, D2, D3, etc). Is it possible to have excel copy an existing worksheet for each of the names listed in column D (and also name the copied sheet accordingly)?

Thanks.


2016-07-30 09:25:31

Barry

@Ramanathan,

I'm not sure quite what you mean by not "disturbing" other worksheets, I assume you mean leaving them in the same relative positions.

The following code will add a worksheet and position at the end of the worksheet tabs. It will then name it "New Tab Name " or whatever you decide to name it as.

Sub AddWks()
Dim wks As Worksheet
Dim NewTabName As String

NewTabName = "New Tab Name"

Set wks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
wks.Name = NewTabName

Set wks = Nothing

End Sub


2016-07-30 09:12:23

Barry

@Ernesto,

The code I gave on 8th July 2016 will change the tab name of a different sheet when invoked. To invoke it automatically whenever there is a change in the cell of the master sheet you just need to invoke the macro using the "Worksheet_Change" event on that sheet, you may or may not want to qualify whether or not the cell that changed is relevant to the tab name or not first.


2016-07-29 07:56:40

Ramanathan

Hi,

If in Summary sheet im going add one new name like Ex(Set2). with tat name one new sheet has to create in that same workbook without disturbing my other working sheets.

is there any coding or etc.,

Please help me on this


2016-07-28 15:36:33

Ernesto

Hello, This is my first time coding. I am using the code in the article and works well when it is used in the same tab. How can I change the name of the tab when a cell changes in another tab? I am sure it is really simple. I would appreciate your help.


2016-07-23 05:34:11

Barry

@Scott,

What you want is tricky, as there isn't an event that is triggered when you change the tab name of a worksheet.

I would tackle this by recording the worksheets name when it is activated (and when the workbook is opened), and then when deactivating a worksheet check to see if the name has changed, and, if so, update your master list. You'll probably need to do this also just before saving/closing a workbook. If the hyperlink is to the codename of the worksheet then the hyperlink will not have to be changed.

There will be a period of time when the tab name will not have a corresponding entry in the master list.This could affect the operation of other macros.

With a small adaptation, the same routine could be used to create your master list in the first place.


2016-07-22 09:25:46

Scott

Hey Barry,

I'm facing an issue similar to Nathan and Stuart below. I have a spreadsheet with 530+ sheets with names that consist of 7 numbers that sometimes have an "A" at the end as well (i.e. 0507171 or 0507171A). What I would like to do is link the names of each sheet to a master list located on a sheet near the beginning of the workbook so that whenever these names are updated, the name of the sheet will automatically updated.

Additionally, all of the items in the master list are hyperlinked to their corresponding sheet, so if possible I would like to have the hyperlinks automatically update as well if the name of a sheet were to change.

I've been working off of some of the code you posted below, but so far haven't been able to get the results I would like. I'm almost pretty new the using VBA so this is a bit of a new challenge for me! Any help is greatly appreciated, thanks!


2016-07-20 06:25:27

Barry

@Terry,

The code I published on 8th July will if you add a new name to the list on the Master List, then subsequently run the code will create a new worksheet with that new name.

The code will not delete a worksheet if the entry in the Master list is deleted (this was not part of the original requirement).

It's not the most elegant or efficient way of doing it as it attempts to create a new worksheet for each entry in the Master list even though the sheet already exists especially if there are lots of entries in the Master list (it deletes the new sheet having found a sheet with that name already exists). A better way would be to create a new worksheet and try to rename it for each entry in the Master List; if successful create a new worksheet and carry on. At the end delete the remaining new worksheet that didn't get renamed.


2016-07-19 15:24:50

Terry Tewell

I like the code, but how can I replicate this if I add a new tab. Right now I would have to cut and paste for each new tab I add.

Thanks


2016-07-18 05:12:44

Barry

@Tina

Yes. You can name a tab based on a cell value on any other worksheet, or even a cell in a worksheet in another workbook.
The exact implementation would depend on your specific requirements.


2016-07-17 09:45:41

Tina

Hi,

Thanks for the information.
I am just wondering whether I can name the sheet tab as a cell value in another sheet?

Cheers.


2016-07-08 06:17:49

Barry

@Stuart

The following code will do what you want, I think. It takes the value in column A in the master list and creates a new worksheet with this name and create a hyperlink to it from the master list. It then also creates a formula to link the data in columns on the master list to cells on the new worksheet (you might want to change these to suit your requirements) and also links the column header label.

Note: if any name in column A is an invalid Tab Name or a duplicate Tab Name then that line entry is skipped and no worksheet is created for it. You can tell which these are on the master list as the entries will not be hyperlinked.

Private Sub CreateSheets()
Dim TabName As String
Dim Sh As Worksheet, NewSh As Worksheet, shMaster As Worksheet
Dim rng As Range, c As Range
Dim i As Integer

'Assumes Row 1 of master list contains headers

On Error Resume Next

Set shMaster = ThisWorkbook.Worksheets("MasterList") 'Change this to be the Tab name of your master list

Set rng = shMaster.Range(shMaster.Range("A2"), shMaster.Range("A" & Rows.Count).End(xlUp))

For Each c In rng
If c <> "" Then
Set Sh = ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)) 'add new worksheet
With Sh
.Name = Left(c, 31)
If .Name <> Left(c, 31) Then 'delete new worksheet if not named correctly, then skip
Application.DisplayAlerts = False
Sh.Delete
Application.DisplayAlerts = True
Else
shMaster.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:="'" & Trim(Sh.Name) & "'!A1" 'create hyperlink
i = 1
Do Until shMaster.Cells(1, i) = "" 'link data from master list to new worksheet
.Range("A" & i).Formula = "=" & shMaster.Name & "!" & Cells(1, i).Address
.Range("B" & i).Formula = "=" & shMaster.Name & "!" & c.Offset(0, i - 1).Address
i = i + 1
Loop
.Columns.AutoFit 'adjust column widths on new worksheet
End If
End With
End If
Next c
shMaster.Activate
End Sub


2016-07-06 09:17:46

Stuart

Barry,

Thanks for the information. I can reduce the name size for the medications so that they are less than 31 characters (for the tab). I can link the other data and am looking for a method to automate naming the tabs.

Thanks

Stuart


2016-07-05 05:24:45

Barry

@Stuart,

The VBA code to do this is relatively straightforward, but I foresee some issues with the restrictions that Excel places on the names that tabs can have; specifically the maximum length is 31 characters and the following punctuation cannot be used , / , * , ? , : , [ , ]. Truncating names to 31 characters could throw up conflicts where a tab name potentially could be duplicated (Excel will not allow this).

Navigating around such large workbooks can be a bit of a nightmare so I would strongly advise creating hyperlinks to each worksheet from the Master worksheet.


2016-07-04 22:21:59

Stuart

I have a spreadsheet that contains a list of 130 medications and want to create additional tabs (130) that list the medication name as the name/title of the tab. Also, each row of the master list, contains information about the medication that I would like to add in specific cells of the medication-specific tab. Are both of these possible with VBA?

Thank you.

Stuart


2016-06-25 11:25:20

Barry

@Nathan,

Put the code below onto the "ThisWorkbook" codepage. What this code does is whenever the cell J1 is edited on any worksheet the macro will run through every worksheet and update the worksheets tab name.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim TabName As String
Dim sh As Worksheet


If Intersect(Target, Range("J1")) Is Nothing Then Exit Sub
On Error GoTo Badname
For Each sh In Worksheets
If IsDate(sh.Range("J1")) = True Then
TabName = "Week " & Format(sh.Range("J1"), "dd-mmm-yy") 'change this line to give the precise
'tab name format that you want
Else
TabName = Range("J1")
End If
If TabName <> "" Then
sh.Name = Left(TabName, 31)
End If
Next sh
Exit Sub

Badname:
MsgBox "Please revise the entry in J1.on worksheet: " & sh.Name _
& Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("J1").Activate
End Sub


2016-06-23 20:55:40

Nathan

Thanks Barry,

Can you perhaps suggest a more appropriate code then?
I just copied this from one of your comments below and hoped it would work.

The date that I wish to set is a formula referencing a Master worksheet in the same workbook and as such the cells in the individual worksheets change as the date changes in the master (so that I can update the workbook easily for each financial year) The only problem I am having is I have been changing all 52 weeks (52 tabs) of the year manually which is a real pain in the arse at the moment.

I do run excel 2010 at so that may be an issue as you mentioned?

Any help you can provide is appreciated!

Cheers,
Nathan


2016-06-22 09:51:57

Barry

@Nathan

I am not sure why is not working correctly, in fact, I'm not sure why it is working at all - the "Worksheet_SelectionChange" event shouldn't work from the "ThisWorkbook" codepage. The code you pasted below would need to be pasted into the codepage of every worksheet to work. However, using the "Workbook_SheetChange" event will achieve the desired effect.

I cannot see that multiple sheets are changed other than by a ripple through of a calculation from one sheet onto every other affected sheet, but testing this doesn't trigger the Workbook_SheetChange event, the Woksheet_SelectionChange or even the Worksheet_Change events on dependent sheets. So something else is at play here. If you'd like to email me your workbook I'd happily take a look.

I've also found a bug in Excel2010 which results in a tab name not being updated when a cell changes. If whilst in editing cell J1 the active sheet is changed, the data in the cell is entered/updated but the SheetChange event isn't triggered resulting the tab name remaining unchanged.


2016-06-21 21:35:38

Nathan2

Hi Barry,

The comment I've posted below is the macro I ran (sorry I just copy and pasted).
Basically I'm trying to dynamically rename 50 worksheets to the weekending date based on a cell that is located in each worksheet.
The cell location that i'm trying to reference does not change between sheets, just the sheet number (e.g. the cell is J1 for each sheet).
I was able to use this code for one sheet but it automatically updated the rest of the sheets to be two days ahead

e.g. Sheet 1 -> 10/7/16 (OK!)
Sheet 2 -> 19/7/16 (Should be 17/7/16)
Sheet 3 -> etc.

Are you able to help me at all?

Regards,
Nathan


2016-06-21 21:29:04

Nathan

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim TabName As String

If IsDate(Range("J1")) = True Then
TabName = Format(Range("J1"), "dd-mm-yy")
Else
TabName = Range("J1")
End If
If TabName = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(TabName, 31)
Exit Sub
Badname:
MsgBox "Please revise the entry in J1." _
& Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("J1").Activate
End Sub


2016-06-11 05:26:19

Barry

There is an error in my original code:

The line:
Set Target = Worksheets("Sheet1").Range("A1") should be changed to Set Target = sh.Range("A1")


2016-06-10 05:45:29

Frank

Ignore me. I was running a bugged script!


2016-06-07 11:02:16

Frank

Hello Barry.
What about using this code when naming a tab based on a cell containing a formula?
i.e. cell A1 contains the formula =weeknum(A2). I want to name the tab whatever week number is returned to cell A1 but using your code I get an error message.

Thanks for your patience.


2016-05-27 11:03:31

Willy Vanhaelen

Oops, typo:
ActiveSheet.Range(D13") must be
ActiveSheet.Range("D13")


2016-05-27 11:00:47

Willy Vanhaelen

@Joe B

In the fhe first macro replace:

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

with:

ActiveSheet.Name = ActiveSheet.Range("D11") & " - " & ActiveSheet.Range(D13")


2016-05-26 16:04:03

Joe B

This is great!
Sorry if this has been covered already...
Is it possible to change this to include the range of two cells?
Essentially I have Company Name in cell D11 and Project Name in D13. I was hoping to have the sheet rename to include both so that it displays as: "Company - Project"

Thank you in advance!


2016-05-24 08:00:32

Barry

@Manu

Put the code below into the "ThisWorkbook" module.

Please note though after you've added one new worksheet the macro will fail, as the macro will attempt to name the next new sheet with the sheet name of the first new sheet which is not allowed. So the value in cell A1 on Sheet1 must be changed before another new sheet is added or something needs to be added to the name to make it unique.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim Target As Range
Set Target = Worksheets("Sheet1").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 ob Sheet1." & Chr(13) _
& "It appears to be invalid " & Chr(13)
Range("A1").Activate
End Sub


2016-05-24 00:23:42

Manu

Hello,

Please help me..


I am trying to write a code to add a new sheet in excel and re-name the sheet with value of the Cell of the first sheet.

Ex: Sheet 1 : A1: Run
New Sheet 2, now should be renamed to value in the A1 of first sheet.

Please help!!


2016-03-02 08:55:01

john burns

I would like to create a macro that assigns worksheet name based on two cells and define where to save it as it will be save to a cloud based dropbox. I can place the template for each worksheet on the individual tablets but don't want them stored locally on the device. All devices will have the same files and ideally cell based naming with date and save location would be most beneficial. Any assistance would be greatly appreciated


2016-02-17 09:05:27

Babu

Using this VBN I can change one sheet tab name,Please tell how can change next pages also by using VBN.I have some words befor other sheet also.
thanks and Regards
Babu.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "D4" Then Me.Name = "INSPECTION - " & Target.Value
End Sub


2015-10-12 08:35:13

hashir

In sheet 1 cell A2, contains a name.
In the same name another sheet is there then, copy cell 39 of that sheet and paste to cell B2 of sheet 1 and contuning this activity until all sheets covered


2015-09-16 12:54:22

Barry

@Patty

The following code should do what you want.

Sub CreateSheets()

Dim ws as Worksheet, wks as Worksheet
Dim rng as Range

On Error Resume Next

Set ws = Activesheet.Range("D1"). CurrentRegion
For Each rng In ws
Set wks = ThisWorkbook.Worksheets.Add
Wks.Name = rng.Value
Next rng
End Sub

where the new tab name would be invalid (too long, invalid characters) or duplicated, the tab name will not be named.


2015-09-16 12:42:52

Barry

@Nam,

The following code should do the job:

Sub RenameTabs()
Dim ws as Worksheet

On Error Resume Next
For Each ws in Worksheets
ws.Name = ws.Range("A1")
Next ws
End Sub

where the new tab name would invalid (too long, invalid characters) or duplicated, the tab name will not be changed, or if the sheet is a Chart Sheet.


2015-09-15 09:00:21

Patty

I am trying to figure out if this is possible.

Say in Excel, in Column D, I have a list of 26 teachers names (D1, D2, D3, etc). Is it possible to have excel create an individual worksheet for each of the names listed in column D instead of having to double-click each worksheet and rename?

Thanks.


2015-09-11 12:37:16

NAM

How can I implement this code for just one excel file and run it on an ad-hoc basis to 'update' the tab names to match cell A1 in each worksheet?


2015-08-19 13:42:47

Ricky

Is it possible to link 2 cells in the tab name. For e.g. cell A1 says "1" and cell A2 says "ABC", then in the Tab name I would like to see "1 ABC". Please advise what modification should I do in the code.
Thank you very much!!!


2015-07-04 11:16:59

AB

Thanks. This worked. However the message box warning did not come up when the referenced cell was blank.


2015-06-25 05:15:03

Barry

@Abhijet

Your "people" comment implies that there are several Users. Therefore it would make sense to put some controls what "people" can do.

Personally, in this situation I would protect the Workbook structure and put all worksheet naming under macro control (you could even restrict what certain Users can do as well). You might want to restrict whether "people" can delete worksheets, change formulas, delete rows/column, etc. etc.


2015-06-24 08:32:43

Abhijeet

@ Berry
I agree to your point but, people will keep changing the names in the respective cell(renaming the tab or in case of spelling mistake etc). And of-course the sheet/ workbook will be protected. so what do you suggest?


2015-06-24 07:31:08

Barry

@Abhijet

What you ask for is entirely possible using the worksheets' codename (and would be a better way for several reasons), but it requires a lot of extra code because as it is not straightforward to create a Worksheet object from the text codename for a worksheet.

Also this can be very confusing for a User who in normal circumstance does not have access to the codenames of individual sheets. For instance re-opening a workbook in which all the sheets have previously been renamed, adding a new worksheet will be given a sheet name of "Sheet1" however, its codename WILL be different (unless the sheet with codename "Sheet1" previously has been deleted) such as "Sheet4". It is possible to get around this but by refreshing the names table but this cannot be done automatically as Excel doesn't have an event on Worksheet creation, so would have to be on a timer which periodically checks for new worksheets.

This is a "horses for courses" if you want something which is very robust/bomb-proof then it'll take a lot of extra coding but it may be necessary if the workbook has several inexperienced Users who are adding/deleting worksheets. You might even protect the Workbook structure and only allow new worksheet creation under macro control - how far do you want to go? If it's just yourself then something simpler (as we already have) might be adequate.


2015-06-24 05:06:01

Abhijeet

@
BERRY
Hi,
Thanks berry this is working.
Can i use the actual name of the sheets instead of using the front end sheet names. for eg: my sheet names are Dist1, Dist2, Dist3... so on. But the back end names are (Sheet2, Sheet5, Sheet9, Sheet 12... so on). with this technique the sheets tabs, can be renamed whenever we want.


2015-06-23 08:55:22

Jason

Go ahead and disregard my last comment. Out of sheer luck, I found another comment asking my question and figured it out!

Thanks again though for the article!!


2015-06-23 08:49:56

Jason

Hi. I'm using the second code you've posted, and it works perfectly fine when I'm only entering in text. BUT, when I try to enter in a date (form: 23-JUN-15... well, any form really, I think it's the numbers), I get the "Bad name" command.

Is there something I can do to get this macro to work with dates? I'm using it for a time sheet.

Thanks ahead of time!!


2015-06-23 07:03:57

Abhijeet

@
BERRY
Thanks berry this is working.
Can i use the actual name of the sheets instead of using the front end sheet names. for eg: my sheet names are Dist1, Dist2, Dist3... so on. But the back end names are (Sheet2, Sheet5, Sheet9, Sheet 12... so on). with this technique the sheets tabs, can be renamed whenever we want.


2015-06-22 06:18:10

Barry

@Abhijeet

To trigger the macro as a result of a change on the worksheet is very easy

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
Dim wks As Worksheet
Dim str As String

If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub

On Error Resume Next
Set wks = ActiveSheet
x = 2
Do Until wks.Range("A" & x) = ""
str = wks.Range("A" & x)
Worksheets(str).Name = wks.Range("B" & x)
x = x + 1
Loop
End Sub

The extra line of code restricts the macro from executing if the change is not in Column B.

NB this MUST reside on the code page of the sheet containing the names.

Note also when you close and reopen the workbook when you add new worksheets it will name them "Sheet1", "Sheet2", etc etc. The macro if run will try to rename these sheets with the names in the list but fail as the name will already exist. You only need to put the new name into the list for "Sheet1" for it to be renamed.


2015-06-22 04:39:29

Abhijeet

@ Berry
Hi Berry, in addition to my previous query. It is working fine, the only issue is you have to create a button where you can assign that specific macro so, i was wondering is there is any way we execute this macro without any button. like when i type the names in the cells, and when a move to next cell, it automatically renames the sheets tab, something like that.
i am using the same macro, pl see:
Sub RenameSheets()
Dim x As Integer
Dim wks As Worksheet
Dim str As String

On Error Resume Next
Set wks = ActiveSheet
x = 2
Do Until wks.Range("A" & x) = ""
str = wks.Range("A" & x)
Worksheets(str).Name = wks.Range("B" & x)
x = x + 1
Loop
End Sub


2015-06-17 05:12:31

Barry

@Abhijeet

I'm glad it now works the way you want it.

There are several tips on this website for combining multiple worksheets into one. But ultimately it depends upon your precise requirements, and he specifics of your workbooks.
Try:
excel.tips.net/T007425_Combining_Worksheets_from_Many_Workbooks.html
excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html
excel.tips.net/T002409_Merging_Many_Workbooks.html
excel.tips.net/C0180_Worksheets.html


2015-06-16 05:12:48

Abhijeet

@
Berry
Hi, thanks for your help, it works file.
i wanted to ask you one more thing, is it possible to compile multiple sheets in 1 excel file. for eg: I have 5 separate sheets (same copy) now i want to compile all the data from that 5 sheets to another excel workbook. Master sheet, with same format of-course? how complicated is it?


2015-06-15 06:17:19

Barry

@Abhijeet

Sorry the attempt at the list lost most of its formatting - please view the screen shot on this link: http://tinyurl.com/on4pd6v


2015-06-15 06:07:24

Barry

@Abhijeet

The following code will rename specified sheets from a list of existing sheet names and new sheet names.

First create a list on an un-used/new worksheet like this:

A B C D E F
1 Existing Name New Name
2 Sheet1 NewName1
3 Sheet2 NewName2
4 Sheet3 NewName3

Note: the first row is just a header and will be ignored by the macro

Put the following macro on the same CodePage as the above list (right-click on the sheet tab and select "View Code").

Sub RenameSheets()
Dim x As Integer
Dim wks As Worksheet
Dim str As String

On Error Resume Next
Set wks = ActiveSheet
x = 2
Do Until wks.Range("A" & x) = ""
str = wks.Range("A" & x)
Worksheets(str).Name = wks.Range("B" & x)
x = x + 1
Loop
End Sub

Note: as before if a sheet with the new name already exists or the name is illegal, then it will be skipped and the sheet will remain with its existing name.Therefore none of the new names should be the same as ANY existing sheet name.


2015-06-15 04:01:41

Abhijeet

@ BERRY
Hi Berry, I appreciate your help but both the macros are not working, i tried. please help me as i am new in writing macros.
I would prefer a macro in which i would specifically mention the names of the sheets whom i want to rename. pl help.


2015-06-13 09:50:51

Barry

@Abhijeet

The following two macros will either rename up to 12 sheets if their existing names start with "Sheet", or in the second macro will rename all selected sheets up to a maximum of 12. In both cases the names will be taken from the list on the current active sheet that reside in cells A1 to A12. If a sheet with the same name already exists then it will be skipped, and the name not used. Also if a particular name is illegal it will be skipped and that worksheet will be left with its original name.

In both cases the macro will end after 12 sheets have been renamed, or all of worksheets have been renamed or if the macro runs out of names.

Note: before using the second macro you must select the 12 sheets to be renamed.

Sub RenameSheets()
'renames sheets beginning with "Sheet"...
Dim sht As Worksheet
Dim x As Integer

On Error Resume Next
x = 0
For Each sht In Worksheets
If Left(sht.Name, 5) = "Sheet" Then
x = x + 1
If Range("A" & x) = "" Or x = 13 Then Exit For
sht.Name = Range("A" & x)
End If
Next sht
End Sub

Sub RenameSheetsII()
'renames selected sheets
Dim sht As Worksheet
Dim x As Integer

On Error Resume Next
x = 0
For Each sht In ActiveWindow.SelectedSheets
x = x + 1
If Range("A" & x) = "" Or x = 13 Then Exit For
sht.Name = Range("A" & x)
Next sht
End Sub


2015-06-13 05:25:34

Abhijeet

Hi Berry,
can you pls give me a sample macro for the same.


2015-06-12 08:45:24

JB

Barry - Thank you. I used your comment and did it simply by creating the following macro in my PERSONAL.XLSB and assigning it to CTRL+SHIT+R:


I'm not real knowledgeable about these things but can usually incorporate others' advice. Thanks again.
====================
Sub RenTab()
'
' RenTab Macro
' Renams a worksheet to the contents of A1, up to 31 characters (Excel's imposed limit)
' Keyboard Shortcut: Ctrl+Shift+P
'
ActiveSheet.Name = Left(ActiveSheet.Range("A1"), 31)
End Sub
====================


2015-06-12 08:43:22

Barry

@Abhijeet

You could select the sheets that you want to rename, then have the macro step through each selected sheet in turn and rename it from the list, or the macro could starting at the leftmost sheet if has not been renamed (i.e. its current name doesn't start with "Sheet") and rename the first 12 using the names from the list.


2015-06-12 05:55:26

Abhijeet

@Berry
Hi, I guess it will be random because i have more then 40 sheets. So the 12 sheets are the selected ones whom i want to rename.its in left side but other sheets are also available on left which i don't want to rename. pl help.


2015-06-11 09:53:08

Barry

@Abhijeet

What you want is quite easy to do but before writing any code you need to decide on what the criteria is to assign which name to which worksheet.

E.g. is it the leftmost 12 worksheets, or
rightmost 12 worksheets, or
sheets with existing name's Sheet1, Sheet2, ...Sheet 12; or
random choice, or
some other criteria.


2015-06-11 09:47:04

Barry

@JB

The line of code:

ActiveSheet.Name = Left(Target, 31)

already does what you want - it takes the leftmost 31 characters if the target cell's content is longer than 31 chars, or the whole value if shorter than or equal to 31.

NB this doesn't check for illegal characters which would result in the erro handler being invokded.


2015-06-11 08:09:00

Abhijeet

Hi,
out of 20 sheets I want to rename only 12 sheets from only 1 sheet. (when i put values in the cells(A1:A12) in sheet 1, that 12 sheets shall get renamed accordingly. is it possible?


2015-06-10 09:56:04

JB

I like e basic macro given in the beginning, and I realize that Excel creates a 31 character limitation to tab names. Some of my A1 values will be >31 characters.

What I'd like to do is have the macro use the first 31 characters if A1 is larger than that.

I assume there should also be logic that does something differently if a worksheet already contains the name, such as adding an incremental number to the end.

Can this be accomplished easily?

I need this to be a macro that I can assign to a keystroke.


2015-05-05 13:58:31

John

I am scouring the earth to find a solution:

I have a Workbook with a Summary Tab.

Summary Tab Contains:
EmplID
Name (80)
Region (9 Regions)
Total Sales

Then a tab for each Sales Person (The tab name reads Smith, L John. There are 80 sales people) the tab includes:
EmplID
Name
OrderID
Sales

Every month I have to split up files into 9 new workbooks, 1 for each region manager including only their sales people.

My idea was to tag the region in each sheet (INDEX MATCH on Summary to bring in Region)

Use the article macro to change tab name to region and then I am stuck... Any help would be really appreciated.




2015-05-01 04:43:29

Sandy

Hi I'm looking to use the macro cell A1 date to change the tab name to that date.
There will be a different dates in each sheet in cell A1 to change each tab in multiple worksheets that have tab names Daily Report (1), Daily Report (2), Daily Report (3), etc up to (20) sheets
thanks


2015-04-23 18:49:04

sherri

I am taking an excel class and when i do the skill based exams, when i click on A1 it automatically goes to A2, but if i am on any other worksheet it works fine. I did live chat with pearson, didnt help, any suggestions


2015-03-25 02:58:42

Veer

I have a requirement where in an excel sheet the first tab data should be populated with the data uploaded in other tab..
And i want only desired columns, and some aggregations on columns to be shown on the first tab, don't want as it is populated with the referred data of other tab.


2015-02-10 16:53:44

Ralph

Thanks Awyatt!


2015-01-30 10:59:52

BW

Thank you for the tip. I am needing a more robust macro for the workbook that I am creating. I am creating a tracking workbook that will have facility names in an array of cells say A3:A15 on a 'Summary' page. I would like for the names to appear as they are in those cells in corresponding order on the worksheets in the book.

If at all possible I would like it just to have 1 button to run it and then it populate all of the names on the tabs.

I appreciate any assistance.

Thank you!
BW


2015-01-07 17:15:48

awyatt

Marta: It is not a limit of the routine, but a limit of Excel. Worksheet names cannot be any longer than 31 characters, and there is no way to change that.

-Allen


2015-01-07 16:27:13

marta

Hello I have a worksheet with a list with all the names to rename 400 Worksheets . I have a vba routine to rename all of them However its fails because the routine does not accept names with are more 31 characters.
How could I fix this error without erasing characters/letters


2014-12-22 20:06:18

Cathy McCullough

I really want to be able to code my tabs based on a lookup table and Google Sheets doesn't support Macros. I could use a sharable worksheet but I'm still not getting this Macro concept with Excel.

Thanks so much
Catherine


2014-09-25 12:15:30

Barry

@ Lance

This is very easily done once you appreciate that the formula you are putting in the various cells is just a string. I note that you've commented out the lines of code using the InputBox function which is how you'd assign a name for a sheet.

Firstly input the tab name into a variable e.g.:

Dim NewTabName as String

NewTabName = InputBox("What name do you want to give the Tab?")

then modify this line of code from:

"=IF('100.RTR'!R[7]C[105]=""ISSUED FOR 30% REVIEW"",30%,"""")"

to:
ActiveCell.FormulaR1C1 = "=IF('" & NewTabName & "'!R[7]C[105]=""ISSUED FOR 30% REVIEW"",30%,"""")"

and repat the same substitution on the other line that reference the sheet "100.RTR".

This doesn't include any error handling code for invalid Tab names though.


2014-09-24 09:54:19

Lance

I've been looking for the solution if there is a solution.

firstly here is my marco:
Sub Macro3()
'
' Macro3 Macro
' 3rd Test
'
' Keyboard Shortcut: Ctrl+e
'
' varCellContent = Application.InputBox _
(prompt:="Choose a sheet by clicking on any cell in it.", Type:=8)

' strDestinationSheetName = ActiveSheet.Name
'Dim myValue As Variant
''myValue = InputBox("Give me some input")

Range("A2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[3],1,1)"

Range("B2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[2],2,1)"

Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF('100.RTR'!R[7]C[105]=""ISSUED FOR 30% REVIEW"",30%,"""")"

Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[8]='100.RTR'!R[7]C[14],IF(LEN('100.RTR'!R[7]C[-2])=9,'100.RTR'!R[7]C[-2],""""),"""")"

Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[7]='100.RTR'!R[7]C[13],'100.RTR'!R[7]C[7],"""")"

Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[6]='100.RTR'!R[7]C[12],CONCATENATE('100.RTR'!R[7]C[9]),"""")"

Range("J2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[2],1,3)"

Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF('100.RTR'!R[7]C[96]=""ISSUED FOR 30% REVIEW"",CONCATENATE('100.RTR'!R[7]C[6]),"""")"

Range("N2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-10],4,3)"

Range("Q2").Select
ActiveCell.FormulaR1C1 = _
"=REPLACE(IF(LEN(RC[-13])=9,CONCATENATE(RC[-13],RC[-12]),""""),3,1,"""")"

Range("R2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]=""DSC"",""DOC"",IF(RC[-8]="""","""", IF(RC[-8]=""DOC"",""DOC"",""DWG"")))"

Range("A2:R2").Select
Selection.AutoFill Destination:=Range("A2:R1000"), Type:=xlFillDefault

With Range("A2:A1000")
.Range("A2:R1000").Copy
.Range("A2:R1000").PasteSpecial Paste:=xlPasteValues
.Value = .Value
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End With

End Sub


my question is there any way that the SheetName (100.RTR'!) can be change/replace by putting one input box? and whatever the input box value all of the sheetname (100.RTR'!) will be change/replace with the input box value.

I hope you guys can help me.

thank you very much.


2014-08-19 05:44:53

Stephanie

I have a workbook that has multiple sheets in it that store data and compile rep information on a master sheet. The last 18 tabs of the workbook display the compiled rep information for each rep to print. I want only the last 18 sheets (rep tabs) to change tab names as I change rep information on the master sheet (the information feeds into each rep sheet in cell A1), the other tabs for the data and the master sheet do not need to change tab names. I am able to change the tab name using the code above as long as I click on each rep tab individually and then click on any cell within that sheet. However, each rep sheet also has a hyperlink back to the master sheet and when I click on the hyper link on any rep sheet I get the bad name error. Putting the name of the master sheet in A1 of the master sheet does not seem to help and that would not be a solutions for the other non-rep sheets. Is there a way to make the coding above only affect the last 18 (rep) sheets and avoid the error when hyperlinking back to the master or any other sheet? Also, is there a way to make the name changes happen automatically when I update the rep names in the master sheet without having to then click on each tab individually to make the change?


2014-07-01 08:50:11

Barry

@MarlonSC

This code name the sheet tabs from left to right. It checks to see if there is at least 7 sheets otherwise it exits. There is also code that causes the routine to exit if A1 hasn't changed, or if A1 doesn't contain a valid date (i.e. a value that Excel interprets as a date).

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static oldRng As Variant
Dim rng As Range
Dim i As Integer

Set rng = Range("A1")
If rng.Value = oldRng Then Exit Sub
oldRng = rng.Value
If rng.Value = "" Then Exit Sub
If IsDate(rng.Value) <> True Then Exit Sub
If Worksheets.Count < 7 Then Exit Sub
On Error GoTo Badname
For i = 1 To 7
Worksheets(i).Name = Format(rng.Value + i - 1, "mm_dd_yy")
Next i
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: this macro must be placed on the code page for the worksheet which has the date in cell A1, this doesn't have to be the first sheet (i.e. the leftmost tab) but will name the tabs from the leftmost. The code could be adapted to name tabs from the current position then to the right, and if necessary adding extra sheets if required.


2014-06-30 14:08:28

MarlonSC

@ Barry,

I love what you've done. However, is there a way to get multiple tabs to be renamed based on a cell value on the first tab? Essentially, I change the date on the first tab, and I have 7 tabs that need to be renamed with the subsequent dates for 1 week. I already have cells within the remaining tabs linked to the first tab, and I entered your code (above) for each tab.

However, I have to manually click on each tab and click anywhere on the sheet to get the names to update. I'd like to bypass that step if possible. Help? Thanks.


2014-06-13 16:48:22

PatL

Thanks Barry. I'll give it a try.


2014-06-11 07:56:31

Barry

@Pat L

Try this (change A1 to either D1 or F1 as required):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim TabName As String

If IsDate(Range("A1")) = True Then
TabName = Format(Range("A1"), "mm_dd_yy")
Else
TabName = Range("A1")
End If
If TabName = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(TabName, 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

If it finds a date in the referenced cell then it formats the name to be put in the Tab to have underscores otherwise it just uses the contents of the cell.


2014-06-10 11:12:04

Pat L

I've created a time sheet to track my comp hours. Cell D1 has the date the pay period begins. Cell F1 contains the formula =IF(D1>1,D1+13,"")for the last day of the pay period.
I used the longer macro above, but my problem is the date format. I've set the category to Date and mm/dd/yyyy. The / character is not allowed on a worksheet tab so I get an error message.
Until now I have manually changed the tab name to mm_dd_yy but it would be great if the tab would auto name using the date in F1. Is there a way to have the macro change the / character to _ ?
Thanks
Pat


2014-05-30 12:49:42

PhilP

@Cathyp

Try this one line of code to be added to your existing sheet creation macro - probably immediately after the bit that creates each sheet.

Range("A1") = Sheets(1).Name

Change the A1 to cell for sheet name and (1) to be the number of the added sheet.

Feel free to email me if required


2014-05-28 10:50:37

Ste

Try opening VB then selecting "Insert" then "Module" and put the code into there so it applies to all sheets


2014-04-24 06:50:11

cathyp

Hi

I wondered if it is possible to do this the other way round and have the cell change to the name on the sheet tab. I have a dated sheet macro that will create as many days as required based on a master template - however, I can't get the day to show up automatically on the sheet - but it does on the tab - any help gratefully received.


2014-04-21 06:16:13

Mohammed

Dear Allen

Thanks for the beutiful and very useful tips.

I hae a formula =MATCH($B$3,Jan!$F:$F,0)That I want to slect sheet name - in this case - "Jan" automatically and column range - in this case - " F:F " as well.

Appreciate if you can help me in this.

Note: I'm not familure with macros.

Thanks


2014-01-16 05:09:24

Anouk

Great help! One small question.. I copied the code and followed your instructions to have an automated change in every new workbook that I open. It worked. I change A1 to A2 (to have the contents of A2 as a tabname) and it doesn't anymore.. What can be the reason for this?
Thanks in advance!


2014-01-05 11:32:23

Dana

Please disregard my last comment. I was using Excel 2007. I found the update to this instruction set for that version of Excel and it worked perfectly.


2014-01-05 11:26:27

Dana

Works for plain text but not when the cell contents are from a formula [such as NOW()].


2014-01-04 13:22:27

Kamal

I tried to do as per the instruction but the sheet is not getting renamed as per A1.... Kindly elaborate.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.