Tom’s Tutorials For Excel: Name your sheet tab same as cell value.

You can rename a worksheet in real time the moment you change a cell’s value.

A Worksheet_Change event can look at the entry, evaluate it for worthiness, and immediately rename the worksheet tab to be the same as what you entered in the cell.

There are 3 important considerations:(1) The proposed name cannot be longer than the 31-character limit for sheet tab names.(2) There cannot be any characters that violate sheet tab naming rules.(3) The proposed worksheet name cannot be the same as an existing worksheet in that workbook because duplicate sheet names are not allowed.

For example, you want a sheet tab to be named the same as what you enter into cell A1.

To establish a Worksheet_Change event for the worksheet where you want this to happen, right-click that sheet tab, left-click to select View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

'Verify that the proposed sheet name does not already exist in the workbook.Dim strSheetName As String, wks As Worksheet, bln As BooleanstrSheetName = Trim(Target.Value)On Error Resume NextSet wks = ActiveWorkbook.Worksheets(strSheetName)On Error Resume NextIf Not wks Is Nothing Thenbln = TrueElsebln = FalseErr.ClearEnd If

'If the worksheet name does not already exist, name the sheet as cell value.'Otherwise, advise the user that duplicate sheet names are not allowed.If bln = False ThenActiveSheet.Name = strSheetNameElseMsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _"Please enter a unique name for this sheet."Application.EnableEvents = FalseTarget.ClearContentsApplication.EnableEvents = TrueEnd If

173 comments on “Tom’s Tutorials For Excel: Name your sheet tab same as cell value.”

with a small addition, (adding “&” after MsgBox “You used a character that violates sheet naming rules.” ) i could run it.

But, i have a bug when i want to use it with a formula in a cell. two things:

1) when you put a formula in cell a1, if the value changes, sheet name does not change automatically. is this due to vba running method?

2) when you put a formula in cell a1, you can change sheet name by correcting cell by F2 and enter it. but, if value would not be changed as a result, excel generates this error (there is already a sheet named. please enter a unique name for this sheet). then excel deletes formula in this cell.

Thank you for pointing out that an ampersand “&” was missing, which I have corrected.

To answer your first question, because the event I posted is a Change event, a formula usually would not be the trigger for changing the sheet tab name. However, you could use a Calculation event that captures the change in a cell’s value that is returned by a formula.

I say “usually” because with the Change event as I posted it, entering the formula =Hello changes the sheet tab name to Hello. But I am sure you are asking about a different scenario whereby the formula in (for example) cell A1 is conditional or relies on precedent cells.

To answer your second question, you could establish a Calulation event that is triggered by the calculation of the cell of interest, without deleting the formula in cell A1.

Thanks for the updated Worksheet_Calculate code. It almost works for me, but not quite – the sheet tab names I want changed (‘target’ sheets) depend on a selection in a ‘main’ sheet. So, after putting the above code in a target sheet whose tab name I need changed, I go to the ‘main’ sheet to a drop down list to choose the required selection, which then automatically makes a change in cell A1 of the target sheets – as the above code you wrote works on the ActiveSheet, the code changes the tab name of my main sheet (as this is where I go to make my selection which activates the change in A1 of the target sheet based on a formula), rather than my target sheet. Is there any way you can amend the above code so that my target sheet tab name changes rather than my main sheet tab name? I’m very new with VBA, so if you could list out the exact changes that I would need to make, that would be greatly appreciated! Thank you.

My first thought is that you might have copied the code into a standard module. It is supposed to go into the worksheet module.

Take another look at what I wrote in my tutorial, in the paragraph immediately preceding the code:

“To establish a Worksheet_Change event for the worksheet where you want this to happen, right-click that sheet tab, left-click to select View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.”

This code is a procedure event. It is not supposed to show up in your macro list.

Supposing for example you want to change the name of the Sheet1 tab to some other name, and your A1 target cell is in, say, Sheet3 , place the code I posted into the Sheet3 module (or whatever sheet you are working on). Then, simply change this line
ActiveSheet.Name = strSheetName
to this
Worksheets(“Sheet1”).Name = strSheetName

My only caveat would be to not code for the existing and soon-to-be-renamed tab name but the sheet’s VBA object name, example
Sheet1.Name = strSheetName

Greetings, and thank you for your clear explanations and useful code examples. I’m having trouble where the above routine (November 23, 2011 at 1:44 am) successfully renames the target sheet, but then continues to run, causing the sheet to recalculate until I get a “stack overflow” and the code stops.

I would like to re-name Sheet1 to match Sheet2!B2, where
Sheet2!B2 = Concatenate(“Tab “, Sheet2!B3)

I’ve tried commenting out the portions of the code that check for name length (< 31 char), illegal characters, and other sheets with the same name.

To easily access your workbook module, in Excel version 2003 or before, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the immediate left of the File menu option. Right-click on that icon, and left-click to select View Code. In Excel versions 2007 or after, from your worksheet press Alt+F11, then press Ctrl+R, find your workbook name in the “Project – VBAProject” left vertical pane, expand the Microsoft Excel Object folder for your workbook, right click on ThisWorkbook and left-click to select View Code. Paste the procedure into the large white area that is the workbook module, then press Alt+Q to return to the worksheet.

Thanks for following my page. You said in your comment that your amended code does not work for you, but I tested your sample code modification and it works for me. Maybe you are testing it in a module for a worksheet other than an existing worksheet whose tab is already named Sheet1. At the point of your modified code insertion, there are no safeguards yet on place for duplicate sheet names, so maybe you are hard-coding the default name of Sheet1 when another actual Sheet1 worksheet exists. In any case, based on your question and attempted workaround, I cannot repeat your error because your code works in the context you described.

On your main sheet, what exactly is the drop down list? Is it data validation? A combobox from the Forms toolbar? A combobox from the activex Toolbox? Is it entered into a cell? If so, what is the cell address? Whatever the case, I would tie the code as a macro to the drop-down selection of the sheet name, with an inputbox that pops up to ask for the new name of the sheet you selected in the drop down. Please provide a few specific details such as I asked for, and I’m sure a solution is possible.

Hi – strange problem: Whether I load the code into a workbook Module or into the Worksheet Code, I cannot convince Excel 2010 that the macro exists. It doesn’t show up in the list of macros to run, and in the VB editor window, hitting “Run” or”Debug” does nothing at all. All other macros I have are working.

What I posted is an example of a Worksheet level event procedure. These kinds of procedures are not macros per se, and they would never show up in the Macro dialog as a list of macros to run. That is because, being an event procedure, they are triggered by an event of some kind. In this case, the event is a Change event. A Change event happens when a change to a cell is made, typically when you enter a value into a cell, or you delete the contents of the cell, or basically when you change the data (that is, type something new, edit something existing, or delete what is in) a cell. Further, the change event in my example is only (as you can see by the first line of code in the procedure) limited to cell A1. It is cell A1 that is being monitored for a change on that particular worksheet, and no other cell. So, type some text into cell A1, such as Hello and (unless there is another sheet tab in that workbook coincidentally also named Hello), your sheet tab for that sheet will be automatically named Hello. Also, be sure you followed my direction in the example. I never said to put he procedure I posted into the Workbook module. It goes into the Worksheet module as I wrote in my paragraph immediately above the procedure code.

I am very new to VBA and I am trying to build a a multitab template that has two pre-existing Tabs called Cover and List. The following Tabs (5 or 6, pre-existing, with some calculations in it) are to be named from the Tab Cover (whose Tab name is automatically taken from it’s cell A1 using your original code above) but from different cells (i.e. B16, B18, B20….etc). These other Tabs are called Sheet3, Sheet4 etc. Is there any way to modify your code to do this?

I tried to copy B16 into Sheet3!A1 and put the original code again in its code but it seems to react only to changes that are not fed by a formula leading to another tab. Since, as you explained, this is not a real macro, I am not even able to run it manually or assign it to a button.

I looked at the other events that can be used as triggers (rather than Change) but none seems to be good for this use (I tried to fiddle with FollowHyperlink but I think I need a little bit more studying VBA before I can test at random 🙁 ).

Would really appreciate your help. I learned a lot already by observing the logic you used to produce the original routine. I found others in internet but none was as cleaver and complete as yours.

I will assume you are wanting changes made to certain cells on the Cover sheet, and only on the Cover sheet, to be what causes other sheets to have their tab names changed.

I further assume your sheet named Cover is VBA sheet code name of Sheet1, and that the List sheet’s code name is Sheet2. And I’ll assume that a change to cell A1 in the Cover sheet will change that sheet name, and changes to cells B16, B18, B20, and B22 on the Cover sheet will change the tab names for sheet code names Sheet3, Sheet4, Sheet5, and Sheet6 respectively.

If by chance you do not know what I mean by “sheet code name”, go into the VBE and hit Ctrl+R. Find the name of your workbook in the Project – VBAProject window. your workbook name will be in bold and look like VBAProject (YourWorkbookName). Expand the yellow folder named Microsoft Excel Objects. You will see that each worksheet has two names. One is the code name, and the other is the name you would see on the tab of the sheet. For example, one sheet (I am assuming) will be named Sheet1 (Cover). So, modify the belwo code in the Select Case block for the proper sheet code names of the worksheets you want to change the names of.

This worked when I tested it, given the aforementioned assumptions. It goes in the module of the Cover sheet **in place of** (that is, substitute this code with) the previous original code you pasted in from my posted example.

Private Sub Worksheet_Change(ByVal Target As Range)
‘Specify the target cell whose entry shall be the sheet tab name.
If Intersect(Target, Range(“A1,B16,B18,B20,B22”)) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
‘If the target cell is empty (contents cleared) do not change the sheet name.
If IsEmpty(Target) Then Exit Sub

‘Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = Trim(Target.Value)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If

I have a cell that holds a value which is two cells concatenated together.

When I changed one of teh values a new result of teh concatenation appears and teh macro should change teh Shhet name to match.

However I am getting teh errr saying that there is already a sheet with that name. The problem is that the sheet name in teh message is one of teh existing sheetnames but not a duplicate of teh value on teh current sheet.

I don’t understand how what you describe is possible, but I do not doubt that you are seeing what you are saying. Still, I cannot duplicate what you describe. By any chance are merged cells involved with any of this.

I am using the INDIRECT command to access the material data I need on another worksheet in my workbook. The problem I am having is if I have a space in the worksheet name AL 7050 T7451 425 Plate the INDIRECT command returns #REF!.

Hi and thanks for your message. I cannot understand how the code does not look at every worksheet in your workbook for a duplicate sheet tab name. It works fine when I tested it. Some numbers are significant to Excel and to computers, especially those with a factor of a power of 2 such as 8^2 and so on. The number 178 is not such a significant number and that many worksheets in a workbook is not excessive. Can you try the code again, maybe on some other sheet like the 137th and see what happens.

I know it long time passed from this topic but I used this macro and my problem is: I should only write in A1 cell to change the sheet name. I mean when I copy & paste data from another sheet to A1 cell it is not work! how can I fix the problem because most of the time data need to be pasted on the exact sheet.

Now then, let’s further say on your Sheet1 codename worksheet, that cell A1 contains the formula which, when it changes, causes the Sheet1 codename to have a new tab name, of, say My Budget or whatever is entered in that other source sheet which the formula in cell A1 depends on.

In your worksheet module, in its simplest form, this would do what you want:

From your original post. Is there a way to change your target cell to take say (3) cells and combine them to name a new tab. For example: A1: type1, A2: type2, A3: type3 and format them to show in the tab with spaces and punctuation?

I have found this, so sorry I know that it is quite an old post. I am trying to get worksheets to be named from a list. The list is a list of student names that will change from term to term etc. So when I change the student name I would like the sheet name to change. Currently I just have student 1, student 2, etc in individual cells. So there could be up to 25 or 30 students and therefore worksheets…

A few considerations:
Do you want a new worksheet to be created as the names are looped through, or do the sheets already exist.
If a sheet for a name on the list already exists, should it be re-created or kept and appended.
Where is this list of names (sheet and range)
Any duplicate names, if so what to do about that, as 2 worksheets with the same names cannot reside in the same workbook.

I am not sure if my original reply went through, however it is not up here anymore so I will try again.

I don’t want new worksheets created, they will be existing. I am creating a reading log for data entry, and will just have a template on each worksheet ready to go.

The list of names is in a separate worksheet called “Class List” which I would like to remain called ‘Class List”. The list is student names and are individual cells and I have allowed for up to 30 students in the list. There should be no duplicate names as I will use a number or something to that effect as I need to know which student is which.

The worksheets will be existing (as they are logs for recording data. I thought it would be best to have the template there rather than creating a worksheet every time and try and put the template in).

The students names are currently on one worksheet called “Class List” in the same workbook, (which I would like to remain “Class List”) and I have allowed for up to 30 students in my list.

Currently I have the list as “Student 1” to “Student 30”, in individual cells. So when we input the students name, I will just put in “Jane Smith” (for e.g.) in place of “Student 1” for example. There should be no double up’s with names as if there ever are two kids with the same name we usually put a 1 or 2 or whatever on the end to distinguish.

I hope I have explained that ok. As you can no doubt tell, I am not a tech person.

Hello Tina, yes this can be done. Hit Alt+F11 to get into the VBE. From there. hit Ctrl+R to show the Project window, where you’ll see the name of your open workbook(s) in bold, such as VBAProject(“YourWorkbookName”). Expand that workbook object and you see a yellow folder named Microsoft Excel Objects. Expand that folder and you see the worksheet object names and the ThisWorkbook module. For example, suppose you have two worksheets in your workbook, where one worksheet has the tab name of Budget and the other has the tab name of Expenses. Suppose you want the worksheet named Budget to change to whatever is in cell A1 of the Expenses worksheet. In the VBE, pay particular attention to the object name of the worksheet that you want to change the tab name for. You know its tab name is Budget at the moment. However, suppose its object name is Sheet1, which you would see in the Project window as Sheet1(Budget). Maybe in your workbook the recipient worksheet (whose tab you want to change) is the sheet object named Sheet5(Budget) or Sheet23(Budget). Whatever it is, make a note of the object name of that sheet. Let’s say its object name is Sheet1. Now, in the Project window, double-click on the sheet name that holds the value in its cell A1 that, when it changes, you want the Budget tab to be changed to. As I said, maybe that worksheet is named Expenses, but whatever it is named, in the worksheet module of that sheet, put in this Change event code:

Of course, there are additional monitors for length (31 characters or less) and illegal sheet naming characters as I posted in my example, but this is the general approach for how a Change event on one worksheet can affect another worksheet.

Or Is there any way to change tab name to a cell value on a protected sheet?

In my case, people input a child ID in Sheet 1 (say B2), and this Child ID will be linked to a cell in sheet 2 (say A2). The second sheet is protected. I want the name of Sheet 2 to change with the Child ID.

Everything you want to do is doable. The compile error is due to the same event being coded more than once in the same module, violating VBA’s rule about that. Post all your sheet module code so I can see what the other error is. Regarding sheet protection, that is irrelevant for what you are doing with sheet tabs. I already gave you the answer about how to change the tab name of one sheet when another sheet’s cell is changed. If “linked to a cell in Sheet2” means the cell in Sheet2 gets its value from a formula in that cell, then you can alternatively use the Worksheet Calculate event in the Sheet2 module to monitor and change the Sheet2 tab name when that cell’s value gets recalculated.

Hi Tom. I wonder if you could help. The code works great, but the cell I want to “name” the tab is on another tab called data. Cell A6 to be exact. How can I change the code in a way it will search for that specific cell. Thank you

Paste the code I posted into your data worksheet module. Change the target cell references (there are a couple of them) from A1 to A6 in the code.

Next, you need to reference the worksheet whose tab name you want to change. To do that, in the VBE hit CTRL+R to show the Project window. Expand the name of your workbook and then expand the Objects folder. Worksheets are objects that have 2 name properties. One name property is is CodeName which never changes. The other name property is its Name which is what you see on its tab. For example, maybe the data sheet is named data on its tab, but Excel regards that sheet object as Sheet2. You would know that by looking at the list of objects and see (for example)
Sheet2(data)
Find the worksheet in the object list that is the worksheet whose tab you want to change when cell A6 is changed on the data sheet. Maybe the worksheet’s CodeName is Sheet5. In my code change this line
ActiveSheet.Name = strSheetName
to this
Sheet5.Name = strSheetName

Genius! Works great. But I need this to happen with 5 more sheets (6 in total).
The values in cells A6:A11 should define the names of sheets 1 to 6.
Should I just had the code you gave for each of the sheets or is there a way to keep the code small and just insert the missing parts?
Anyway, I am really amazed with your work. Thank you

Replace the code you have with this, that goes into the module of the worksheet where you are changing the values in cells A6:A11. Works when I tested it.

Private Sub Worksheet_Change(ByVal Target As Range)
‘Specify the target cell whose entry shall be the sheet tab name.
If Intersect(Target, Range(“A6:A11”)) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
‘If the target cell is empty (contents cleared) do not change the sheet name.
If IsEmpty(Target) Then Exit Sub
‘Disallow the entry if it is greater than 31 characters.
If Len(Target.Value) > 31 Then
MsgBox “Worksheet names cannot be more than 31 characters.” & vbCrLf & _
Target.Value & ” has ” & Len(Target.Value) & ” characters.”, _
48, “Keep it under 31 characters.”
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
‘Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
‘Verify that none of these characters are present in the cell’s entry.
Dim IllegalCharacter(1 To 7) As String, i As Integer
IllegalCharacter(1) = ” / ”
IllegalCharacter(2) = ” \ ”
IllegalCharacter(3) = “[”
IllegalCharacter(4) = “]”
IllegalCharacter(5) = ” * ”
IllegalCharacter(6) = “?”
IllegalCharacter(7) = “: ”
For i = 1 To 7
If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
MsgBox “You used a character that violates sheet naming rules.” & vbCrLf & _
“Enter a name without the ” & IllegalCharacter(i) & ” character.”, _
48, “Not a possible sheet name !”
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
Next i
‘Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = Trim(Target.Value)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If
‘If the worksheet name does not already exist, name the sheet as cell value.
‘Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then

Dim ws As Worksheet, cn As Integer, tRow As Long
tRow = Target.Row – 5
For Each ws In Worksheets
cn = Val(Right(ws.CodeName, Len(ws.CodeName) – 5))
If cn = tRow Then
ws.Name = Target.Text
Exit For
End If
Next ws

I seem to be having trouble finding a solution to this, hopefully you can help.
I need to rename existing worksheets 4 though 10 to the contents of cells B14, B29, B44, B59, B74, B89, and B104 from worksheet 2. Sheet4 would be the contents from B14, sheet5 would be B29, etc. I also need the macro to run anytime those cells are modified.

Can you simplify this code for me? I have a macro that duplicates a template that I have created and then renames the tab based on a list. What I need is this:

I could like to put in a macro that does not allow the user to enter any names into the list that violate the rules of naming a worksheet. I know that it already exists in the code that you have provided, I just don’t know VBA well enough to pull out what I need. My list is in column A in a worksheet called “opportunity pipeline”. Can you give me a modified code to accomplish this?

If I understand your set-up correctly, you want to monitor a list of allowable worksheet names that are listed in column A. In that worksheet’s module, with the code I posted, it might be as simple as changing this line:

If Target.Address <> “$A$1” Then Exit Sub

to this:

If Target.Column <> 1 Then Exit Sub

If there’s more to what you need for this to work with your sutuation, please post back.

Ok, so the code below is what I used. Its not working. I already have a macro that duplicates a tab and renames the worksheet, so I do not need this macro to do that. I simply want this macro to let the user know that they cant use certain characters in the cell and it must be under 31 characters. So what is wrong with this?

‘Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = Trim(Target.Value)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If

This code does exactly what you say you want. I just tested it and it works without any problem. Keep in mind this is technically not a macro but a procedure, specifically a Change event in the worksheet module, and only one Change event is allowed in a module. If you already have a Change event in your sheet module, you need to combine that one and this one into a single Change event procedure.

Tom, I just have to say thanks. I’ve been scouring around trying to find something to function exactly as this does, but only yours is turning out right. This has put a really nice touch on my final product.

Thanks for the nice message, Dennis! It’s always great to hear from visitors about my material that works for them, so I can continue to improve my website. More content coming in 2017. Happy New Year!

I went through all the codes above and still cant get any of them working in my file. My file contains 10 tabs in total. The first tab name is “Cover” (sheetcode name Sheet1), 2nd tab name is “Lookup” (sheetcode name Sheet2), the rest of the sheet names have same tab names & sheet code names (viz. Sheet3, Sheet4, Sheet5…Sheet10). In the “cover” tab, I select the region name in cell “C2” basis which the market data validation list in cells “B5:B12” is updated (I am using indirect formula in data validation to return only those markets in drop down list that belong to that region – so if I selected region “Europe” then the drop down list (indirect data validation) in cells B5:B12 (of cover tab) would now show me a list of only European markets. The user can then select the right market (from drop down list) in cells B5:B12. Lets say user upon selecting the region “Europe” in cell C5 (from drop down list) then selects “UK” from the dynamic drop down list in cell B5, selects “France” in cell B6, selects “Italy” in cell B7 and that is it. In such case the tab names of Sheet3, Sheet4 & Sheet5 should automatically change to “UK”, “France” and “Italy” while the remaining tab names would remain as “Sheet6″,”Sheet7″,”Sheet8″,”Sheet9” & “Sheet10” since the user only defined/selected 3 markets (out of 8) in cells B5:B12 (of cover/sheet1 tab).

If a different user changes the region to Latin America (in drop down in cell C5 of cover tab) and then selects the appropriate markets like “Argentina” (from dynamic drop down list) in cell B5 (of cover tab), “Brazil” in cell B6, “Colombia” in cell B7 and “Mexico” in cell B8 then the previous tab names (Sheet3 showing as UK, Sheet4 showing as France & Sheet5 showing as Italy) should now be renamed once again to Argentina (for sheet3 from UK), Brazil (for sheet4 from France), Colombia (for sheet5 from Italy) and Mexico (for sheet6 where sheetcode & tabname was same earlier) while the rest of the sheetcode & tabnames remain same (i.e. sheet7, sheet8, sheet9 & sheet10).

Finally, if the same user, now selects “North America” region (from drop down list in cell C5 of cover tab) and then uses the dynamic drop down list in cells B5-B12 to just select 2 markets (“US” in cell B5 and “Canada” in cell B6) then all the sheetcodes from “sheet3” to “sheet10” (who have the Asia Pacific market names viz. Japan, Australia, Hong Kong, Singapore, Taiwan, Thailand, Phillipines & India) now get renamed to (US, Canada, Sheet5,Sheet6,Sheet7,Sheet8,Sheet9 & Sheet10).

Hope that requirement made sense. It is a total dynamic tab name renaming dependent on market name selection by the user (via a dynamic drop down list in cells B5:B12 of cover tab). The dynamic drop down list existing in cells B5:B12 is dependent upon the “Region” selection by the user in cells “C5” of the cover tab. I really liked the vba codes on this page and they helped me with previous exercise but I just hit the wall with what I am trying to do now. Any help would be really appreciated 🙂

Just a small correction.. Region name selection is in cell “C2” of cover tab. I may have incorrectly mentioned cell C5 but cell “C2” in cover tab has the drop down list showing region names and when the user selects the region then the 2nd drop down list (in cells B5:B12 containing market list for that region) gets updated based on indirect data validation list).

Thanks for following my blog. This is quite a detailed explanation for your question, which I appreciate but which I won’t have the opportunity to digest and answer.

May I suggest you post your question on this forum…http://www.mrexcel.com/forum/excel-questions/
…which has many Excel experts around the clock available to help provide an answer, sooner than I would be able to in this case. The website is free, and it’s easy to register if you haven’t done so already.

So I used part of your code along with some other code I had and am running into the following issue. The tab name is in cell C4. If I delete that value the name stays but if I then change something else on the worksheet the VBA craps out. If there is a value in C$ everything works fine.

the first part of my code is hiding columns based on a cell and then that is followed by the tab name with your empty cell coding added to the top of that.

I’m pretty new at this VBA thing so I’m sure something is out of whack but cannot pinpoint it. Any help would be greatly appreciated.

At first glance, the reason why your Change event “craps out” is that you do not have a designated target cell. In my example, I have this as the first line of code (preceded by the comment line):
‘Specify the target cell whose entry shall be the sheet tab name.
If Target.Address <> “$A$1” Then Exit Sub

So in your case you’d presumably want to have (which you currently don’t have)
‘Specify the target cell whose entry shall be the sheet tab name.
If Target.Address <> “$C$4” Then Exit Sub

Otherwise, if you can clue me in to the line of code that errors (it would be highlighted in yellow when you click the Debug button), it will help understand where the error exists.

I did not test all your code, as I say this is my first impression of what is missing in your code as compared to what’s needed that I posted in my code.

Thank you for the quick response. I had found an workaround plus another issue I had to resolve about ignoring other sheets so I used this code and it all seems to work. Is there a way to name a sheet something if “C4” is blank instead of just leaving what it was named?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If ActiveSheet.Name “Program List” And ActiveSheet.Name “PR DATA” And ActiveSheet.Name “Lists” Then
On Error GoTo Out
If Target = Range(“C4”) Then ActiveSheet.Name = Range(“C4”).Value
Out:
End If
End Sub

Hello. Thanks for sharing your VBA knowledge. I was able to successfully implement your code by placing it in the Workbook module. However, the changes happen only when I type something, or retype the formula which references the name of the tab of the active worksheet from another worksheet. Could you help me with a workaround? The main worksheet is named CP. In CP I have a list of the different worksheet names in one column opposite of which I place a value of 1 or 0 if I want to hide or unhide the worksheet (I have a separate macro for this). But to do this I used generic names for tabs which my users find difficult to remember. Now the same macro still works if I dynamically change the tab name. Problem is the other tab names don’t update automatically even when the formula is in place =CP!Ab5. As I said, I need to either retype formula or just type something. Is there any other trigger I can use to do this automatically? Please help. Thanks.

Your question is similar to several other comments on this post regarding the use of a formula instead of a manual change to a cell. It sounds like you need to have a Calculate event instead of a Change event. My question is, where should the code go, and that depends on how your workbook is designed. On your main CP worksheet you have a 2-column list of worksheet names and either a 1 or a 0 next to each name.

Are there worksheets in your workbook OTHER THAN CP that do NOT need to have their names changed, if so, what are their names?
What range on your CP sheet is this 2-column list?
Explain how cell AB5 on the CP sheet has its value changed.

What you want to accomplish is do-able but a more thorough explanation of your workbook’s design is needed in order to suggest the code you’d need.

There are other worksheets that do not require their names changed. In any case, what I do is lock the cells so that they do not become edited, then protect the sheets. In AB5, I use concatenate to combine the values of two different cells from two different worksheets in order to create a unique worksheet name (e.g. G1 for 1st Grading Period plus the section thus the name G1-Section 1) Then I reference the name G1-Section in cell A3 of all worksheets using the formula =CP!Ab5. When teachers create their student list of all their classes in one worksheet, AB5 of worksheet CP is automatically updated, including cell A3 of the other worksheets, but the tab names of said worksheets remain the same not unless I retype the formula =CP!Ab5 or type directly the section. For the other worksheets that do not need renaming, I leave the pertinent cell empty so they are not affected. I got it that some of the answers are found in previous post but could not make heads or tails anymore of the answers because of the varying queries. Thanks in advance for your help. I am an elem teacher by the way.

PS. The worksheet names are contained only in column AB (AB5, etc). Opposite that, in column AC is where I put the value 1 or 0 to hide or unhide each worksheet using a macro I placed in worksheet CP. I tested it already. So long as the names in column AB correspond to the tab names, the macro for hiding/unhiding sheets works perfectly. The problem is how to automatically rename the tabs each time the value of column AB changes which is referenced in cell A3 of the other worksheets. Thanks again.

P.S. The range of the worksheet names in CP is AB5:AB65. Each one is referenced in different sheets: =CP!Ab5, =CP!Ab65 etc. The other worksheets that do not need renaming are either found before AB5 or after AB65. What teachers can do is just change the section in the list found in another worksheet which is referenced in column AB of sheet CP. Thanks again. Hope my explanation is clear enough.

So you are saying that on 61 different worksheets (61, because the range of AB5:AB65 comprises 61 cells), on each of those 61 worksheets is cell A3 which also contains the name of the worksheet From the outside looking in based on your explanations, the way the dots connect for G1, C3, and AB whatever is not clear. If you want, which I don’t normally offer, you can send me a replica of your workbook so I can see what is going on in there and how this process works. You might be going about it the best way or there might be a better way, but in any case the problem is definitely solve-able.

I have a similar question to the last post. I have a workbook that uses a list of Names (on Sheet1) that change each day depending on who is working and who is not. Some days will have 10 people working and others will have 30 so all 30 tabs will not be always be used. I would like to have the tab names change when the workers are listed on Sheet1 A2:A31. I’ve tried some of your formulas above and haven’t had complete success because each tab references A1 which has a formula tied to the list on Sheet1. I think i need the calculate event that you listed but don’t know how to add it into your existing formula. Is there a way to do this?

So how do you know which worksheet should respond to a change in which cell? For example, if you change the name in cell A8, how is Excel to know that a particular sheet that you have in mind is the one that should now have its tab be the same name?

Each tab has a formula in cell A1 that is tied to A2-A32 on Sheet1. A2-A32 on Sheet1 are where the names will be selected each day as the employees are scheduled. The names will vary day by day which is why I want the sheet names to change each day depending on who we have working.

Right-click on your Sheet1 worksheet tab, and left-click to select View Code. Paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Be aware that there is no error checking in this code to monitor duplicate named in A2:A32 of Sheet1. That means you will get a run time error if there are duplicates in that range. That of course can be included but this is the general idea based on what you said you want to do. Or, you can play it safe and delete whatever is in cells A2:A32 before you enter updated names.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intRow%
For intRow = 2 To 32
With Cells(intRow, 1)
If Len(.Value) > 1 Then Sheets(intRow).Name = .Value
End With
Next intRow
End Sub

This worked perfectly. Thanks so much for the help!! Is it possible to make the sheet name revert back to the default Sheet3, Sheet4, Sheet5 ect, if the cell is blank? I’ve noticed that once I assign the names, the sheets keep that name even if the cell contents are cleared. I don’t understand how writing macros works so if this can’t be done I can work around it. I can also add a list of default names in Column M next to my master list of employee names if the formula can reference that column when the cell is blank. Hope this makes sense.

Ok, it seems to work but every time I select a name a run time error pops up saying Script out of range. I click end and everything seems to be ok. I clear the contents and the sheets default back to the original sheet name but I keep getting the same run time error ‘9’: Script out of range.

Disregard the last post. I rebuilt the workbook from scratch and added your code to that and everything seems to be working great now. I’m not sure how I got the other version screwed up but either way, it’s working now. Thanks for all of the help!!!

I have a simple excel document named report.xls. I could really use a vbscript that retrives the cell value on the last row of the sheet in colomn B. The content is always date and time in this format: “17-01-2018 08:02:59”.
I need to rename the excel document with the date information like so: “17012018.xls”

Assuming your range of A2:A whatever is populated with as many worksheets as you want to rename, and those worksheets’ tabs are already named with the default “Sheet” prefix, this will do what you want, which I just tested and know works:

Sub NameSheetz()
Dim cell As Range, LastCell As Long, xNumber As Long
With Sheets("Sheet1")
LastCell = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each cell In .Range("A2:A" & LastCell)
xNumber = cell.Row
Worksheets("Sheet" & xNumber).Name = cell.Value
Next cell
End With
End Sub

I recommend you identify the worksheets using the Sheet object name instead of the tab name in case you want to rename already-renamed sheets, but this macro does what you asked for.

That is exactly why I suggested in my reply that you consider using the worksheet codename instead of the tab name. Of course, the macro can only be run once because it expects the tab names to start with “Sheet”, and the tab names will be different as soon as the macro runs the first time. Check to be sure that your worksheet codenames (not tab names, but codenames) are Sheet2 for the sheet that is or was of the tab name Sheet2, that the Sheet3 codename worksheet is or was the same as the Sheet3 tab name, and so on. Then post back if you have verified that.

See this link on my Twitter page for Excel, and then click the image itself to see it more clearly, for the difference between the two naming properties if you are unsure of what I am talking about. Codenames never change so they are more reliable in your case because they will always refer to the worksheet object no matter what that worksheet’s tab name gets changed to.https://twitter.com/TomUrtis/status/994307204617388032

I have data I drop into a workbook, refresh the pivot, then double click on each period’s result count line to build a separate sheet per period. When that new sheet is generated I would like to reference the new cell on the newly created sheet that holds that sheet’s period name as the sheets name.

I was hoping I could pick say in the pivoted sheet cell D1 (Identify the naming reference cell such as cell AA2) that would contain the new tab’s name for each new sheet as it is created. Maybe have a macro button to perform the double click action on each row result on the pivot and auto name each new sheets with whatever exists in their sheets AA2 cell. Is this possible?

Hi, I’m really new to all this and I’ve tried reading through all your previous posts but I’m very confused. I have a ‘master’ worksheet that contains names and results, these results are updated monthly from a downloaded document (copy and paste). I have then created separate worksheets within the same workbook for each name which imports their results from the main sheet. So far so good. However the names can change from month to month. I created a concatenate formula which gives me Surname, Forename and a ‘date’ created from text which I use with a small macro to make the tab name.

I have a few problems, the first is that if a name disappears from one month to the next it can’t seem to cope and leaves the name in place putting all the results under the wrong tab names. The second is that when I add the new data to the master (with a new ‘date’ to make a unique name) it doesn’t change the tab and then issues errors. When the code highlights an error, it usually highlights the Application line. Finally, if a new name appears it moves the names along, and the end ones don’t have a worksheet to go to, I can’t seem to figure a way to solve these issues. Is there anything that I can do, or am I asking too much?

Wow, a lot going on there. From the sounds of it without seeing your workbook…

• I don’t think the SelectionChange event is what you should be using. If this is a monthly action based on imported data, then running a macro on that data would be better.

• Without references from you about what is where, I can only guess and would probably be wrong as to how your master worksheet is arranged. Maybe names are in column A and results are in column B but without knowing for sure, too hard to say.

• I don’t get what one of your core problems actually stems from. It ***sounds like*** a core problem is naming a worksheet tab based on either a person’s name change or a date change. And I’m not sure what you do when 2 different people name John Smith are listed in your downloads. It’s also unclear if data on existing worksheets is to be appended or deleted and refreshed with new data.

Basically, more info from you would help about what you are working with and where it is located on this or that worksheet.

What you are trying to do is do-able, but how to go about it depends on several factors.

You might want to post your question on a popular Excel forum to which I sometimes contribute, but which has many Excel experts visiting around the clock so you will have access to a wider audience who can also help. That link is https://www.mrexcel.com/forum/excel-questions/ which is free and easy to register.

Thanks for the code, it works perfectly. The only issue I have is, my excel tab name is coming from a cell (E3) that is a drop-down box and every time I select a different name it comes up with the message:

‘There is already a sheet named ‘XX’.
Recalculate the formula in cell A1 to return a unique name.’

This happens even if I do not have another tab with that name selected. Do you know how I may be able to adapt the formula to avoid this message every time?

If you are on some sheet and you have a drop down box (I assume that means you have data validation) in cell E3, first, what is actually being data validated? Presumably the drop down you speak of is a list of values, and if those values are proposed sheet names and another sheet in that workbook is named the same as an item in that drop down list that you select, then yes, the code is doing its job properly. The issue, speaking to your comment “This happens even if I do not have another tab with that name selected” is not that a particular sheet need be selected, but that any sheet in the workbook, whether selected or not, is already named whatever is selected in the drop down list.

But maybe I am totally misinterpreting your question. If so, please post back with a further explanation of what you are doing. There should be a solution to whatever it is.

No problem, but can you please confirm:
• what is the codename of the worksheet whose tab name is Sheet 2.
• how does the entry in cell A13 on Sheet 1 get there — with a formula in cell A13, or is it manually entered, or is it copied and pasted, or some other way.

Wow Thanks for the quick response. Its so wonderful to be able communicate with someone so knowledgeable. Ok, I’m creating a Tie Point list. Sheet 1 will contain all the information for sheet 2 and so on. Sheet 2 codename is TP-XXX.

The information for lets say Tie point 101(TP-101) is manually entered into sheet 1 cell A13, and so on for Tie point 102 (TP-102), sheet 1 cell A14. I simply copy and paste the information from cell A13, to sheet 2 cells D4, to get formulas like (=’TIE IN LIST’!$A$13).

The problem is when I use your 1st code from above it wont transfer info to taps. it doesn’t like the formula in sheet 2 cell D4. each Tap should read TP-101, TP-102 and so on. it works fine without the formula in the cell D4.
Thank you so much for your help Tom.

The codename for your sheet 2 could not be TP-XXX. It must be some other codename. Hit Alt+F11 and press Ctrl+R. There in the Project-VBAProject window, find the name of your workbook. Expand the Microsoft Excel Objects folder. In that folder you will see your worksheet names. My guess is that you will see 2 names per worksheet. For example, if you named the worksheet whose tab originally was Sheet1 as TP-101, you might see
Sheet1(TP-101)
and for the worksheet whose tab was originally named Sheet2 you might see
Sheet2(TP-XXX)

What I want to know for sure is what the codenames of those worksheets are.
In my above examples, the two codenames are Sheet1 and Sheet2 but the respective tab names are TP-101 and TP-XXX.
I care about the codenames and which sheet tab names they correspond to.

Ok, yes I see two names, The name for sheet 1 is: sheet1(TIE IN LIST)
the name for sheet 2 is: sheet2(TP-xxx)
sheet 3 name is: sheet3(TP-xxx(2))
and so on.
The tap names currently are, Tab 1: TIE IN LIST
Tab 2: TP-XXX
Tab 3: TP-XXX(2)
and so on.

Tom it works perfectly!
I have now been trying to add tabs (“$A$13″,”A$14”) and so on, to the code, but I can’t seam to get it to work.
How would I add additional Tabs to the Coad so, Sheet3(TP-XXX(2)), Sheet4(TP-XX(3)) etc. will also be linked to sheet1.

Thank you for all of your assistance on this board. I read through all of the Q&As here, I have tried combining some of the code you provided and its not working. (I am very green with VBA). What I need to do is change the tab names of 17 different tabs based on 17 different dynamic cells on one tab (data changes based on a drop down). I see how you have instructed others to use the Change function, but I need the formula based on Calculate so that all tabs update automatically (the formula for 1 tab worked perfectly btw…) Thank you again!

Thanks for your comment.
It sounds like you can use the worksheet level Calculate event.
Without knowing the particulars of your workbook, the procedure in your worksheet module where the formulas are would be:

Private Sub Worksheet_Calculate()
'your code, depending on which 17 sheets whose tab names you are changing
'would get which value from the 17 dynamic cells being recalculated.
End Sub

As James said, many thanks for this excellent tutotial. I’ve tried many of the VBA scripts You’ve provided on this page and learned so much! But I still can’t get quite the right combination pieced together to do what I want.

In simple terms, I would like my first worksheet (named “index”) to contain the months of the year in A1:A12. These will be typed in depending on how a fiscal year runs – it may be Jan-Dec or it may be Jun-May. The next 24 worksheets will be named with those names plus some fixed text so it will look something like this: Jan GL – Jan P&L – Feb GL – Feb P&L – Mar GL – Mar P&L etc.

This code you wrote comes close to doing what I want but has none of the clever error correction your other code has:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim intRow%
For intRow = 2 To 32
With Cells(intRow, 1)
If Len(.Value) > 1 Then Sheets(intRow).Name = .Value
End With
Next intRow
End Sub

I would also need to reference cells on those worksheets in other formulas. Can I use the CodeName of the worksheet (Sheet1, Sheet2, etc) in my formulas?

Taking that last question first, if your formulas are native Excel worksheet formulas, the answer is no, you cannot use the SheetName project name, but you could if your formulas were UDFs.

The codes I posted in this tutorial were all driven by Change events. In your case, this is not very practical because you can have (for example) a sheet tab named May P&L when the month of May is in cell A5 of your Index sheet today, but you will want that same name in a differently indexed worksheet if May is in cell A9 of the Index sheet. It seems to me that what you could do is to have those 24 sheets created and named first, because month names will always stay the same. Then, the Index position of the sheets can change as you change their order in A1:A12 on the Index sheet. But even then if it were me, I’d do the reordering on the Index sheet and instead of a Change event, I’d have a macro do the sheet-shuffling to correspond their index placement to the order you entered them on the Index sheet. This way you can have (using the May example again) May in cell A7 but you type May into cell A5 which at the moment (a temporary moment) there are two May’s (one in A5 and the other in A7) but a moment later you enter June in cell A6 and July in cell A7. In other words, when the month names are uniquely entered, the macro can then do the shuffling job to put the sheets in the order they show up on the Index sheet.

If I am understanding what you are saying, renaming of the worksheet tabs would then be unnecessary. I will always have a Jan-Dec set of worksheets but their order would change depending on where a fiscal year would start and end. The only thing that would need to be dynamic would be quarterly report worksheets which would have to link to the correct monthly sheets based on how the fiscal year would run – and that could be set from the index page. This might be an easier solution than the way I was thinking about it. Thanks Tom!

Hi Lee, you’re welcome. I am not the biggest fan of index sheet reliance, although as you describe the arrangement would work IF no one moves the sheets around. But I am a big fan of the SheetName property which is VBA-driven and which always refers to a sheet tab name no matter where the sheet is indexed. If you have what you need using the Index route, so much the better, just saying that using the SheetName identifier is also a possibility. Thanks for following my blog!

Tom Urtis, You are an awesome man. thank you so much for this assistance. i got my sheet to work using this code. I want to learn VBA so i can write my own but for now, i follow you. LOL thank you again

“it didn’t work” tells me nothing without more details. It worked for me. I assume you put the macro in a standard module and not in the worksheet module where your Selection event was that you posted earlier.

Thanks Tom,
I have a workbook with 20 sheet + first sheet; so the first sheet is the entry sheet. I usually enter lab numbers in the entry sheet (Sheet1); however,that sheet has a column from (B15:B34); each cell of that column (B15:B34) represent a name of one sheet. for example, if I want to give first sheet an name i will enter that lab number in cell (B15),and if the second sheet I have to put the lab number in cell (B16) ans so on .
I used this method be unfortunately , giving me sometimes define error when I click on any cell on the sheet.

The code is already there to allow only 31 characters. I would keep it like that.

As to replacing the illegal characters with a dash, you can do a replace in the code or beforehand but I would not do that either. Capture the illegal characters either in the code as I have posted the code, or use data validation to disallow those illegal character entries in the first place.

Tom,
You seem to be the guru for this, and I think I have a relatively easy question, but can’t work it out.
I have a mastersheet and would like to name new sheets based on C1, Z1, AR1, etc. You’ll notice that each cell is 16 cells to the right (always in row 1). The other thing that makes this possibly complicated is that C1 is merged to X1 likewise Z1 is merged to AP1 which seems to be adding spaces to my sheet names (which makes no sense to me). I would prefer this doesn’t run automatically, because the sheet names may change order.

(1)
You wrote:
“You’ll notice that each cell is 16 cells to the right…”
Actually, from C to Z is 24 cells to the right and from Z to AR is 19 cells to the right. Different distances and both are different than your stated 16 number.

(2)
Merged cells are a pain and should be avoided at all costs because they are nothing but trouble. My suggestion would be to use Center Across Selection instead of merging cells. Please explain why you are using merged cells instead of Center Across Selection. If there is an unusually compelling reason why merged cells are required, they can still be worked with but they cause many programming issues.

Hi TOM,
thank you for all these information. I was able to use your code and get it working, however i have one minor issue.
I have a master worksheet named “POs” with a list of PO# in cells A1:A20. I have added your code in the “POs” worksheet. The code then renames my other 20 worksheet with the PO#s listed. However the only way to get this to work is that I have to update each PO# separately(lines A1:A20) . If i do a mass update (by copy pasting the 20 PO# into my “POs” worksheet then the other worksheets don’t get renamed.
is there a way around this. I need the code to support mass update of the referenced cell range

Your messages are inconsistent. First you said you had “other 20 worksheets” for names in (presumably because of your word “other”) a 21st sheet in cells A1:A20. Your code shows only 19 sheets to be named and it’s not clear if the sheet on which the cells from A1:A (either 19 or 20) should be renamed too. Please clarify exactly what you are doing and what sheet holds the 19 or 20 names and if that sheet is one of the 19 or 20 to be renamed.

hi sorry for the confusion.
There are 20 worksheets in total. one of the worksheets is named POs (which is sheet 2). This sheet contains 19 PO Numbers. The PO numbers are in cells A2:A20 of the PO worksheet (or sheet2).
I don’t need sheet2 to be renamed. I need the other 19 worksheets ,sheets1 and sheet3 through 20 to be renamed based on Sheet2 A2:A20 cells. The code works only if I enter cells A2 through A20 one by one. But if I do a mass update the worksheets dont get renamed

Still not clear.
You write, “one of the worksheets is named POs (which is sheet 2).”
And then you write “…of the PO worksheet (or sheet2).”

What does that mean?
• Is tab of the worksheet containing the 19 PO numbers named POs?
• Is tab of the worksheet containing the 19 PO numbers named PO?
• Is tab of the worksheet containing the 19 PO numbers named Sheet 2?
• Is tab of the worksheet containing the 19 PO numbers named Sheet2?
• Is tab of the worksheet containing the 19 PO numbers named whatever it is named and the worksheet object codename is POs?
• Is tab of the worksheet containing the 19 PO numbers named whatever it is named and the worksheet object codename is PO?
• Is tab of the worksheet containing the 19 PO numbers named whatever it is named and the worksheet object codename is Sheet2?
• Is tab of the worksheet containing the 19 PO numbers named whatever it is named and its index number is 2?

It is also a question as to why a sheet 2 (either by tab name, codename, or index number) would be the second sheet and the name of a first sheet of some kind needs to be changed and skip the second sheet of some kind and resume with a third sheet. That sounds like an index issue (how the sheet tabs are arranged from left to right) which is the least reliable way to be sure about which worksheet is properly receiving the name it should.

And is that name a tab name or a codename.

At this point, I respectfully suggest that you clarify what you are working with, and post your question at the MrExcel’s questions forum. I contribute to that forum too, but you you will find a wider and more available collection of Excel experts who can assist you further.

We used your original post to rename our worksheets and it worked wonderfully.

We have a similar setup to Allen in that the “Summary” page is where the text is entered and transferred through your code to rename the 30 following tabs.

Hoping you can help, we expect users will delete text in the rows not required on the summary page and setoff the unique name error. Is there something we can put in place such as an auto-entry to prevent this?

Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = (.Text)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If

If bln = False Then
Sheet3.Name = strSheetName
ElseIf Sheet3.Name .Text Then
MsgBox “There is already a trade named ” & strSheetName & “.” & vbCrLf & _
“Change the name to a unique name.”
End If

(1)
Why monitor changes to all cells in A5:A200 if you only care about cells A5 and J5 per your two Select Case lines.—I care about all values in all cells from A5-A200 and J5-J200. The only way I could get the case to work was by entering in A5 and J5 in the second lines of the macro…..it currently works, however I could have done something wrong, this is the first time I’ve ever created anything like this before.

(2)
What exactly is “CopyRename”?
CopyRename is the Macro that I created that copies the master tab. So when A5-A200 or J5-J200 is populated with one of the above terms, it triggers the macro “CopyRename” and duplicates the tab requesting a name be entered.

(3)
This drop down list that you mentioned, first, I assume it is data validation, but can you confirm that.
The drop down list I created includes all of the values listed above in the macro. I build insurance codes for an insurance company and these are all of our available benefits to be built onto an insurance plan.

(4)
More importantly, are those items such as ACUM, AFTR and so on the ***ONLY*** values allowed in the cells.
You can type other values in the cells, however, it will not trigger the macro to build a new “master” tab and rename it. It’s only when these values are entered that the macro is triggered.

(5)
Are there worksheets whose tab names are those drop down items? I’m not clear on what you are doing and what all these cases are for and why only 2 cells (A5 and J5) are monitored for changes.
Basically, for every code my team creates, I need a new auditing tab (MASTER) tab to be created for that code. I’m trying to get it to automatically name the tab to match the values listed in A5 and B6 or A200 and B200.

Is there a way that I can attach the file? I truly appreciate your help!

So you are saying that you want to have select cases for every individual cell in A5:A200 like you do for A5 and J5? I don’t understand how J5 (or column J for that matter) figures into anything Change-wise if you only care about monitoring A5:A200. And I don’t understand what a “new master tab” is and what it would look like when it gets created as the result of a change to a cell. This is more of a project to show an Excel developer and maybe have them show you how to do it or have them do it, depending on its complexity that I don’t fully comprehend.

Thank you for your code, I’m using the below as the starting point of what I am trying to achieve;

Private Sub Worksheet_Change(ByVal Target As Range)
‘Specify the target cell whose entry shall be the sheet tab name.
If Intersect(Target, Range(“A6:A11”)) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
‘If the target cell is empty (contents cleared) do not change the sheet name.
If IsEmpty(Target) Then Exit Sub
‘Disallow the entry if it is greater than 31 characters.
If Len(Target.Value) > 31 Then
MsgBox “Worksheet names cannot be more than 31 characters.” & vbCrLf & _
Target.Value & ” has ” & Len(Target.Value) & ” characters.”, _
48, “Keep it under 31 characters.”
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
‘Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
‘Verify that none of these characters are present in the cell’s entry.
Dim IllegalCharacter(1 To 7) As String, i As Integer
IllegalCharacter(1) = ” / ”
IllegalCharacter(2) = ” \ ”
IllegalCharacter(3) = “[”
IllegalCharacter(4) = “]”
IllegalCharacter(5) = ” * ”
IllegalCharacter(6) = “?”
IllegalCharacter(7) = “: ”
For i = 1 To 7
If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
MsgBox “You used a character that violates sheet naming rules.” & vbCrLf & _
“Enter a name without the ” & IllegalCharacter(i) & ” character.”, _
48, “Not a possible sheet name !”
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
Next i
‘Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = Trim(Target.Value)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If
‘If the worksheet name does not already exist, name the sheet as cell value.
‘Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then
Dim ws As Worksheet, cn As Integer, tRow As Long
tRow = Target.Row – 5
For Each ws In Worksheets
cn = Val(Right(ws.CodeName, Len(ws.CodeName) – 5))
If cn = tRow Then
ws.Name = Target.Text
Exit For
End If
Next ws
Else
MsgBox “There is already a sheet named ” & strSheetName & “.” & vbCrLf & _
“Please enter a unique name for this sheet.”
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

I want to edit this so that a range of predefined sheet names are changed based on a range of cells (not necessarily sheets 1 to 6). Changing the range of cells is straightforward but can you provide some guidance on what I would need to change in the above code if say I wanted to rename sheets 5 to 10?

There are a few ways to interpret your question so can you please describe a different way what you mean.

For example, my code does reference a range of cells but you wrote “I want to edit this so that a range of predefined sheet names are changed based on a range of cells”.

Another example, what does “rename sheets 5 to 10” mean? Sheet tabs are named Sheet5, Sheet6, and so on up to a tab named Sheet10? Or do you mean the index of sheets (their positional sequence from left to right in the workbook from the fifth to the tenth), Or do you mean range A5:A10 because you said what I quoted in my first example. Or do you mean VBA sheet codename Sheet5 to Sheet10.

1) I’m happy with the first edit for the range of cells – so no input from you required here I just included for reference.
2) I was referring to sheet tabs “sheet5, Sheet6, Sheet7, … etc the exact sheet number range I am comfortable I can edit it, is how to bring a range of sheets into your code that I was struggling with.
3) I’m new to VBA but I assume it is possible to assign a VBA constant to a particular sheet number, so that it can be referenced by this, although this would be useful probably isn’t necessary to achieve the above but I’m happy to define the sheet names with a constant in VBA if that makes the solution easier/more efficient but I would need to know the syntax to achieve this.

Below is the code I’m currently using which does achieve the desired effect but

1 its a bit clunky (I assume a loop could be used),

2 it requires an action to activate (macro button or similar which I have incorporated as a temporary fix) and

'Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = strNewName
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If

'If the worksheet name does not already exist, name the sheet as cell value.
'Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then
Sheets("Sheet" & xCounter).Name = strNewName
Else
MsgBox "There is already a sheet named " & Sheets("Sheet" & xCounter) & "." & vbCrLf & _
"Please enter a unique name for this sheet."
End If

‘Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = strNewName
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If

‘If the worksheet name does not already exist, name the sheet as cell value.
‘Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then
Sheets(“Sheet” & xCounter).Name = strNewName
Else
MsgBox “There is already a sheet named ” & Sheets(“Sheet” & xCounter) & “.” & vbCrLf & _
“Please enter a unique name for this sheet.”
End If

Thanks I cannot get my head around this sorry to keep bothering you, the below code now only works bizarrely for sheet 33 but no other sheets. It also only runs when I click the play button in the VB application so it looks like I need a trigger event of some sort. Any idea

‘Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = strNewName
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If

‘If the worksheet name does not already exist, name the sheet as cell value.
‘Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then
Sheets(xCounter).Name = strNewName
Else
MsgBox “There is already a sheet named ” & Sheets(xCounter) & “.” & vbCrLf & _
“Please enter a unique name for this sheet.”
End If

Having had a bit more of a play with it the later tabs will only rename if the tab before has also been altered I guess its an issue with the duplication check.

‘Verify that the proposed sheet name does not already exist in the workbook.

I also think there is an issue with the value of xcounter not resetting once the loop is completed I’ve tried defining a value before the loop starts for xcounter with varying success.

Thinking a bit more logically an easier solution may be just to make every tab rename to a fixed cell. i.e. every tab has the desired name included in cell A1, I can then vary the value of cell A1 from a control sheet. The issue I foresee with this is I would need some kind of trigger event to update all of the sheet names?

That’s what I thought you had… The desired new tab name in cell A1 of those 21 worksheets. I’m tied up today and tomorrow but if you’re still stuck on Friday, send me the workbook on Friday and I can take a look at it then.

Hi, Tom! Thanks for the excellent tutorial! I’ve read through all your replies, but am not quite finding the answer to what I’m trying to do. Feel like I’m missing something simple, but not sure what it is.

Let me start off by saying, I am totally new to VB, so go easy on me. 🙂 What I’m trying to do is build a backup time card tracking spreadsheet for myself and my team. There will be 26 tabs, each covering two weeks’ worth of time. The start date for the two weeks will be in C2 and the end date in Q2 on each sheet. I only enter the date on the first tab at the beginning of the year. The format is yyyy-mm-dd, though when I click on the cell, it keeps showing as m/d/yyyy, no matter what I type, so was expecting it to give me errors on the first tab. All the other dates in the workbook are calculated as the previous day + 1, including C2 on the other tabs. I do not have a tab with a list of dates, though if necessary, I guess I could add it. (That was going to be my next attempt, since you have so many more examples of that above.)

Ideally, what I’d like is for the tabs to be named “[Start Date} to {End Date]”, but I’d settle for getting either Start or End to work! I put your original procedure against ThisWorkbook as described above. All the tabs currently have their original names, i.e. Sheet1 (Sheet1). The only thing I changed in your code was to change
If Target.Address “$A$1” Then Exit Sub
to
If Target.Address “$C$2” Then Exit Sub

Compiles with no errors, but doesn’t change any tab names. I’ve tried removing the date to see if it’d at least give me an error. That didn’t do anything either. I am working in Excel O365, if that makes a difference.

I read your message a couple times, and I’m not clear on a few points if you can help me understand.

(1)
You say there are 26 tabs in your workbook. Does that really mean 26 worksheets or are there any other worksheets in the workbook besides the 26.

(2)
You say each tab covers two weeks of time, and the start date is in cell C2 and the end date is in cell Q2. Two weeks equals 14 days but C2:Q2 is 15 cells, not 14, so what is the reason for that.

(3)
Upon what action exactly is it, and where would that action be (such as cell address and sheet name), which would trigger the result you want to achieve.

(4)
A basic point here that I should have put as #1, are you sure that your macro settings are set to allow you to run VBA code in your workbook.

The formatting issue won’t be a problem, but (and I’m going easy on you as you requested, which I would have done anyway) I first would want to get a handle on exactly what the workbook design is from a sheet count standpoint, and a cell range standpoint, and a security settings standpoint. If you don’t know how to verify your macro settings, don’t worry about not knowing, just come back here and say so if that’s the case and I will show you how. I never know how basic or un-basic to get with replies to someone totally new to VBA as you said you are, which I mention because on the face of your description as I read it, it sounds like you did things correctly, but maybe you are missing a loop to nail all the sheets.

In any case, post your code if you need to, and let me know about those 4 items. I’ll be offline until tomorrow but will check my blog comments tomorrow if you get back to me between now and then.

1/ I have a total of 26 worksheets in the workbook.
2/ I have a sub-total column in between the two weeks, therefore an extra column.
3/ At the beginning of the calendar year, I want to put a date in Sheet1:C2. The dates on Sheet2:C2-Sheet26:C2 are calculated based on the previous day +1. Once that date is saved the first time, it shouldn’t get updated again until the following year. (Just so you know, we’ve been using a version of this workbook for four years as a way to easily keep track of your time during the week, to enter into the actual time card system on Fridays. It’s just been that for the last four years, I’ve gone through and manually updated the dates every year. Was trying to simplify.)
4/ I think so. Have saved as an .xlsm and on my Macro Security Settings, I have enabled “Trust access to the VBA project object Model.” I do have “Disable all macros with notification” enabled, but you said this is a process, not a macro, so thought that would be OK.

Whether it is a macro or a procedure, as far as Excel is concerned, the fact remains that programming code is somehow involved.

Change your macro setting to “Enable all macros (not recommended; potentially dangerous code can run”) and try running the code again. If that works, then decide if you want to keep that setting, or the setting above it (Disable all macros except digitally signed macros).

I rebooted this morning. When I opened the spreadsheet, it told me that macros were disabled, so I enabled them. Changed the data in Sheet1!C2. No change. I went into Macro Security and enabled all macros. No change. Tried it with the option for digitally signed, too. No change. Maybe I just don’t know how to run the process? In the VB Console, when I try to Run, it asks me to create a macro, but you said this isn’t one. In reading above, it looks like the trigger is changing the value in the cell. Is that not correct?

The first sub-routine is:

Private Sub Worksheet_Change(ByVal Target As Range)
‘Specify the target cell whose entry shall be the sheet tab name.
If Target.Address “$C$2” Then Exit Sub
‘If the target cell is empty (contents cleared) do not change the sheet name.
If IsEmpty(Target) Then Exit Sub

This is not a macro, it is an event procedure. It gets triggered when, on the worksheet whose module it is in, cell C2 is changed.

So, for the worksheet where you want to enter something into cell C2, if you right click onto that sheet tab, and you select View Code, you would see that Worksheet_Change event. If you do not see that procedure there in the worksheet module where it should be, that means you have it in a different worksheet module, or more likely, you have it in a standard module such as where macros go. Maybe a module named Module1 or whatever. But in any case, if you do have it in a standard module, then you have the code in the wrong place. Event procedure codes must be housed in the worksheet or ThisWorkbook module depending on it it is an worksheet level event (as this one is) or a workbook level event.

Take the code out of the ThisWorkbook module and put it in the worksheet module. Also, maybe you are working on a totally different Excel version or maybe a Mac. What you should be seeing as a popup menu item when you right click a sheet tab is View Code as I said, not View Source as you said.

In a previous message you wrote:
“At the beginning of the calendar year, I want to put a date in Sheet1:C2. The dates on Sheet2:C2-Sheet26:C2 are calculated based on the previous day +1. Once that date is saved the first time, it shouldn’t get updated again until the following year.”

To me, that means you enter one date one time once a year. That one date one time once a year date is entered into cell C2 of the first worksheet.

That further means, for example, the formula in cell C2 of the second sheet would resemble =Sheet1!C2+15 and so on for the other 24 sheets.

That further means, once you enter that one date in cell C2 on the first sheet, all the dates on all the worksheets cover all 2-week periods of the year.

That further means, the Change event code (in your worksheet module of the first worksheet where it belongs) would, upon the manual entry of that one date in cell C2 of that first worksheet, handle the naming of each tab in a loop that is part of the Change event code to handle each worksheet’s tab name change. Programming code from one sheet can do things to hundreds of other sheets in the same workbook, so no, I do not mean and never said that the code should go into each of the 26 sheets.

Moving it made it do something! Now, I’m getting “You used a character that violates sheet naming rules. Enter a name without the “/” character.” I’m typing my date as 2020-03-21, but if you recall, I said that when I click on the cell, it shows as 3/21/2020. It switches to that no matter what format I put it in.

That said, I put something that wasn’t a date into the cell, I got the same error, but it updated it to what I entered, but only updated that worksheet. And the cell blanked out due to the line in the code for error handling.

If this can’t be automated, I can just go back to doing it manually, since it’s only once a year. I appreciate the time you’ve spent trying to get this working. I’ll wait to hear if you think you know a way around the /, and if not I’m going to give up this time around. Thanks for your help!

That got it to work on the first tab! All the dates updated, but the other workbook names didn’t update. Going to the C2 cells on the other pages and “editing” them by double-clicking and then entering didn’t change anything either.

I think I’ve taken up enough of your time. Am going to try adding a new worksheet that has the dates listed explicitly and doing a lookup to that page as you described above. If it doesn’t work, I’ll to back to updating by hand. Have a great weekend!