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.
Private Sub Worksheet_Change(ByVal Target As Range)
'Specify the target cell whose entry shall be the sheet tab name.
If Target.Address <> "$A$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
ActiveSheet.Name = strSheetName
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
Hi,
Congratulations about above code. it’s very useful.
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.
Private Sub Worksheet_Calculate() With Range("A1") If Len(.Value) = 0 Or Len(.Value) > 31 Then Exit Sub 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(.Text, (IllegalCharacter(i))) > 0 Then MsgBox "The formula in cell A1 returns a value containing a character that violates sheet naming rules." & vbCrLf & _ "Recalculate the formula without the ''" & IllegalCharacter(i) & "'' character.", _ 48, "Not a possible sheet name !!" Exit Sub End If Next i 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 ActiveSheet.Name = strSheetName ElseIf ActiveSheet.Name < > .Text Then MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _ "Recalculate the formula in cell A1 to return a unique name." End If End With End SubHi Tom,
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.
I am having exactly this problem. I cant find a suggested solution to it in this discussion.
Have you found a solution to it? Many thanks
When I copy this code into a module it doesn’t show up in my macro list. Thoughts?
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.
Thank you for your code to automate the renaming of the tabs when they are referencing a cell that is based on a formula! It works well….mostly.
I’m having one problem:
The formula I’m using to base the name of the sheets in the workbook (B) on is referencing a cell in another workbook (A). The cell I’m referencing obtains the naming information from two columns in that workbook(A); one that contains a name of a facility, and another that automatically provides a sequential number based on whether there is text in the facility name column or not. This then reports the information below to at tab in Workbook (B) which is referenced in each of the sheets in the workbook (B).
Format would typically appear as follows:
# Facility Name Page 4 Tab Name Page 5 Tab Name
1 Facility A PG 4 – Facility A ProjSheet PG – 5 Facility A BldUtil
2 Facility B PG 4 – Facility B ProjSheet PG – 5 Facility B BldUtil
3 Facility C PG 4 – Facility C ProjSheet PG – 5 Facility C BldUtil
4 Facility D PG 4 – Facility D ProjSheet PG – 5 Facility E BldUtil
If I Identify that a facility needs to be added between A and B, and add it in the source sheet, an error is generated because B, C and D already exist in the existing tab names.
I’m wondering if there is a way to first restore any tabs with “ProjSheet” and “BldUtil”in their name to their original name (Codename?) and then run the renaming macro on the individual sheets so that there isn’t the conflict? FYI: My workbook has 78 sheets, 30 of which have “ProjSheet” and 30 which have “BldgUtil” in their names.
Wow. I am reading this a few times to follow all the factors you explained about, trying to understand how all the dots connect. This is not a criticism of you at all Ron, but it seems that this arrangement is more complicated than it needs to be. I started getting lost at “This then reports the information below to at tab in Workbook (B) which is referenced in each of the sheets in the workbook (B).”
What caught my attention in a good way is this:
“…and another that automatically provides a sequential number”
To me, sequential means unique — a non-repeated number in a (supposedly) increasing sequential progression. If that is the case, the unique nature of the sheet name should be resolved. But obviously it is not resolved, so something else is intruding on that process.
Based on your last paragraph with the questions, it sounds like you want to programmatically start from scratch name-wise and then rename the sheets based on their project CodeName (not their tab name), which I always highly recommend. A CodeName never changes but it always refers to the same worksheet object regardless of the tab name.
Not being familiar with your workbook, generically speaking, if it were me, I would create a list of the 78 sheets’ CodeNames, which are unique, and run a macro that looks up the Facility Format combination that matches the CodeName’s sheet whose tab you want to rename. This way, you would not need to temporarily rename the tab names with some interim boilerplate syntax.
That is my best shot at a first reply. If you get stuck on anything specific, please feel free to post back.
Thanks for the reply! You are absolutely correct and I was overcomplicating the issues.
I’ve combined the two workbooks to minimize the complexity. I have the following formula in the cell I’m referencing to obtain the sheet name:
=”PG 4 – “&XLOOKUP(A3,’Property Listing’!B11:B54,’Property Listing’!A11:A54)&” “&XLOOKUP(A3,’Property Listing’!B11:B54,’Property Listing’!C11:C54)&” ProjSht”
That looks for the property name selected from a drop-down list in cell A:1. It looks for that name in column B in the property listing sheet and returns both a unique property number, and an abbreviated property code which looks like this and is located in cell C3:
PG 4 – 6 DDE ProjSht
That part works perfectly and changes automatically based on the selection from the drop-down.
The code below has been pasted into the individual sheets I want to automatically name based on the contents of cell C3:
‘The following code changes the name of the worksheet to the input found in cell C3
‘This code came from https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-name-your-sheet-tab-same-as-cell-value/
Private Sub Worksheet_Calculate()
‘Specify the target cell whose entry shall be the sheet tab name.
With Range(“C3”)
If Len(.Value) = 0 Or Len(.Value) > 31 Then Exit Sub
‘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(.Text, (IllegalCharacter(i))) > 0 Then
MsgBox “The formula in cell C3 returns a value containing a character that violates sheet naming rules.” & vbCrLf & _
“Recalculate the formula without the ”” & IllegalCharacter(i) & “” character.”, _
48, “Not a possible sheet name !!”
Exit Sub
End If
Next i
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
ActiveSheet.Name = strSheetName
ElseIf ActiveSheet.Name .Text Then
MsgBox “There is already a sheet named ” & strSheetName & “.” & vbCrLf & _
“Recalculate the formula in cell C3 to return a unique name.”
End If
End With
End Sub
The code is trying to update the tab names, but two things are happening:
1.) The sheet name for my ‘Property Listing’ tab is changing to the value of the first tab that needs to be renamed.
2.) I’m receiving the following error box(es): “There is already a sheet named xxxx. Recalculate the formula in cell C3 to return a unique name.”
If I rename the “Property Listing” tab and then go to C3, if I hit F2 + enter, it updates the tab name correctly.
Any ideas as to how to prevent this? I’ve checked and there is no VBA code in the ‘Property Listing’ sheet.
Wow, this subject has generated years of posts. Reading through the ongoing discussion and trying to wrap my head around all the permutations of how this tool can be used is mind blowing. Kudo’s TomU for all your patient help & creative thinking!
I utilized the “Private Sub Worksheet_Calculate()” procedure code you posted November 23, 2011. Even though I barely understand how it all works…after a few false starts it WORKED! However, I’ve run into a snag.
BACKGROUND
I’m using your code on an invoice spreadsheet for my wife to track billing for consultation calls. Previously I kept one tab named “Blank Invoice” for her to copy & paste into a new worksheet for each client / month (when copied they are automatically named “Sheet 1, Sheet 2, Sheet 3, etc.”). She would rename each tab with “yr.d-Client Initials”; e.g. “21.4-BS”. I automated invoice to auto-fill Client Name & new Invoice # (using =IF, LOOKUP & “filename”) to interpret Sheet Name once the date was entered. This proved too confusing – big challenge was that tab name must be manually formatted correctly for formulas to work.
I wanted to try a different approach using your code to update Sheet Name by using “Calculate()” code triggered by calculation in “Invoice#” cell.
– Invoice # calculates using ClientName&TodaysDate&TotalClientInvoices (e.g. 21.4-BS 16)
– Client Name input is controlled by Data Validation to select client name from list
– Client Name list pulled from “Totals” worksheet (tracks client hours & income).
– Selecting Client Name triggers =NOW() in the “Date” field & an Invoice# calculation
This all worked EXCEPT…when I copy “Blank Invoice” to a new sheet it automatically names the new sheet with same Sheet Name which causes an error because you can’t have 2 worksheets with same name.
Is there a way to write the code to NOT rename new sheet tab, but leave as default (e.g. “Sheet 1, 2, 3…”) as long as trigger cell calculates to “ “ (blank)? But rename sheet tab to desired value once Client Name is entered (which would trigger Date & Invoice # cells to calculate results needed to create worksheet tab name).
Thanks for your compliment.
There are a lot of moving parts in your description. Without seeing your workbook and so not knowing what cell addresses belong to the Invoice# cell or the Client Name cell or the NOW cells, it looks to me after reading your message a few times that maybe a quick fix would be to devise a way to uniquely name a worksheet tab sans Client Name.
So when your Client Name cell is empty (again, if I understand the mechanics of your workbook), one way to ensure a unique name could be to:
(1) Decide upon a dedicated cell on some worksheet as a unique name helper cell. Let’s say it is your Totals sheet, cell Z1.
(2) Manually enter the number 100 in cell Z1 of the Totals sheet. It would be the only manual entry in that cell you would make.
(3) During the course of your Calculate event’s execution, when the condition of the Client Name is deemed to be a null string (meaning the cell is empty or has no value in it), a code snippet can be:
Dim lngNextNumber As Long
lngNextNumber = Worksheets("Totals").Range("Z1").Value + 1
ActiveSheet.Name = "Sheet" & lngNextNumber
Worksheets("Totals").Range("Z1").Value = lngNextNumber
How do I provide a sample workbook? Don’t see a way to send attachment to reply.
I was hoping my description would be more clear to a reader than it was, but no matter how I wrote it out it still looked more complex than it should.
Thank you for taking the time to look over my question! Either I did not ask it clearly or I do not understand your suggestion. Either way I will try to restate my goal more clearly and give more specific cell info.
BASICALLY
I want a “Master Invoice” sheet that can easily be copy & pasted into new worksheet to create a new unique invoice. Each invoice sheet tab name (which I hope to automate) must contain client initials & yr.mo because each invoice has client notes that must be quickly accessed & referred to, sometimes months later.
The 2 challenges at this point are:
1. If “Master Invoice” is renamed before copying to a new worksheet, the copy immediately becomes renamed with whatever name my “Master” Invoice had, which Excel does not allow. However, it appears that “Calculate()” code does nothing if reference cell calculates to “” so it leaves tab name as whatever last named; so as long as I leave cells blank BEFORE I copy & paste sheet it does not try to create two sheets with same name. But if I wait to rename after I copy sheet I run into challenge #2…
2. The “Calculate()” code worked with the “Master” invoice, but code appears to be only associated with “Master” sheet and does not work once copied to another sheet so I probably entered it in the wrong place. Can code be entered to apply to any new sheet created in that workbook?
The 4 parts of the puzzle are:
Assuming Client is Bob Smith and Bob’s 16th invoice was created in April 2021…
C7 = Client Name (triggers calculation in G5 & G4, results used in Tab Name)
G5 = Today’s Date (enters today’s date & provides yr.mo for Tab Name)
G4 = Invoice# (concatenates yr.mo, client initials & invoice# – e.g. 21.4-BS 16)
Sheet Tab = ideally the 1st part of Invoice# – e.g. 21.4-BS
FORMULAS FOR G5 “DATE” & G4 “INVOICE#”
“Date” entry in cell G5 automated with:
=IF(C7=””,””,NOW())
How it works: Cell C7 is “Client Name” entry cell. G5 remains blank until client name is entered. Once client name is entered it triggers =NOW() calculation to enter today’s date.
“Invoice#” in cell G4 automated with:
=IF($C$7=””,””,RIGHT(YEAR($G$5),2)&”.”&LEFT(MONTH($G$5),2)&”-“&LEFT($C$7)&MID($C$7,FIND(” “,$C$7&” “)+1,1)&MID($C$7,FIND(” “,$C$7&” “,FIND(” “,$C$7&” “)+1)+1,1)&” “&DATEDIF(“12/1/2019″,$G$5,”m”))
How it works: If cell C7 (Client Name) is blank, Invoice# G4 remains blank. If C7 contains a Client name the Invoice# is generated in G4 using yr.mo from G5 (Date Field), then “-“, then 1st & last initials of Client Name from C7, then a space, then counts months from client start date. Client Start Date currently written into formula (i.e. “12/1/2019”) but eventually will pull “Start Date” from client data on “Totals” sheet) to assign number of invoices for that client. e.g. “xx.xx-xx 16”
Hope I haven’t muddied the water further…:)
Is it possible to do this with the cell reference (ie. $A$1) being on a different worksheet? I’ve tried referencing the usual way but it doesn’t work.
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.
My Excel is set to "Automatic" calculation of sheets.
Please let me know if you have any ideas for me to try.
Many thanks,
Christian Crowley
Washington, DC
What is your code? May e you are using a Calculation event that involves a volatile function somewhere, instead of using a Change event.
Tom
how can this code be modified to cover any changes of name in any worksheet in a workbook?
You’d place the procedure in the workbook module for a workbook-level sheetchange event. Example, substitute the procedure name
Private Sub Worksheet_Change(ByVal Target As Range)
with
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
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.
Tom
that worked beautifully! many thanks
JeffC
Hello,
This code is exactly what I was looking for. Thanks.
But if I the user leaves the target cell empty or delete the entry, I want a msgbox to pop-up and ws name to go back to its original code name or something specific.
Your code:
‘If the target cell is empty (contents cleared) do not change the sheet name.
If IsEmpty(Target) Then Exit Sub
I tried this instead:
‘If the target cell is empty, show this msg
If IsEmpty(Target) Then
ActiveSheet.Name = “Sheet1”
MsgBox “Sheet name will presume.”
Exit Sub
End If
doesn’t work, it still keeps whatever was input before.
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.
Hey Tom,
Thanks for your prompt reply. Mine wasn’t working because I was deleting the cell entry. If the entry is backspaced, then it changes the tab name whatever I want.
My Code:
‘If the target cell is empty, sheet name will remain same.
If IsEmpty(Target) Then
ActiveSheet.Name = ActiveSheet.CodeName
MsgBox “Sheet name will presume.”
Exit Sub
End If
Now, I want to add another function for ‘deletion’. Like IsEmpty. So, if the user hits ‘Delete’ from keyboard for the target cell, active sheet would presume codename.
I appreciate your help.
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.
Thanks for following my Excel page.
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.
Never mind — I missed the point that this sheet code autoruns when the cell is edited.
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.
Thanks
Andy
.
Thanks for following my Excel page.
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
‘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
Select Case Target.Address
Case “$A$1”: Sheet1.Name = strSheetName
Case “$B$16”: Sheet3.Name = strSheetName
Case “$B$18”: Sheet4.Name = strSheetName
Case “$B$20”: Sheet5.Name = strSheetName
Case “$B$22”: Sheet6.Name = strSheetName
End Select
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
HI
I am trying to use your macro
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!.
Thanks for following my Excel blog page Mark.
Without seeing your formula, I’m guessing you’re missing an apostrophe before and after the sheet names in your formula. Those apostrophe characters, themselves encased somehow inside double quotes as you’ll see, are necessary with INDIRECT for referring to worksheets whose tab names contain spaces.
Below are links to two examples of INDIRECT examples I posted previously. Take a close look at the formulas. Notice at the start of the formula there is a double quote character, then an apostrophe, then the sheet name, then near the end of the formula but immediately before the ! character is another apostrophe. Also note where the last double quote character encases the formula name, example in this link…
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-one-formula-returns-value-of-the-same-cell-on-multiple-worksheets/
…this formula shows how that is constructed:
=INDIRECT(“‘Sheet”&ROW()-1&”‘!”&$E$1)
And here’s another example:
http://www.atlaspm.com/toms-tutorials-for-excel/tom-s-tutorials-for-excel-viewing-all-worksheets-with-one-indirect-formula/
-Tom
Hi Tom
I just copied and paste your code to VBS module and it works. Thank you very much. However, it only works for 61 sheets and not the rest (there are 178 sheets in my workbook).
Thanks very much in advance.
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.
Hi Tom,
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.
Best regards
Saleh
I am trying to get VBA to write the following formula to an Excel 2007 Worksheet cell:
ActiveCell.FormulaR1C1 = “= Concatenate(“Pg. “,’Worksheet1′!$I$2)”
not sure how to get around the “Pg. ” issue? Any help would be appreciated.
Maybe this:
ActiveCell.FormulaR1C1 = “=””Pg. “”&’Worksheet1′!R2C9”
Hi Tom,
I realise it’s been a long time since this was posted so sorry about that!
Is there a way to do with if the value of the cell in question is found using a formula?
E.g. My cell takes it value from another worksheet in the workbook but it won’t update the sheet name when I update the other worksheet.
Do you have any way around this?
Cheers
Ali Dean
Use the Calculate event in your worksheet module for that.
Suppose the worksheet whose tab you want to change is codename Sheet1. NOTE: Codename is NOT necessarily the same as tab name. Take a look at this link to see what a codename is if you are not sure.
http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-vba-help-usually-right-sometimes-wrong/
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:
Private Sub Worksheet_Calculate()
Sheet1.Name = Range(“A1”).Value
End Sub
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?
Hi There
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.
Hi there
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.
Thanks for you help it is much appreciated.
Kind Regards
I asked you for the range where the list of names is, but you still have not answered.
Also, if there are more sheets in the workbook besides Class List, how would the code know to not name those sheet tabs.
Have you checked to make sure that the mystery range of 30 cells on the Class List sheet do not contain student names that are longer than 31 characters. You can use data validation for those cells.
Hi there
Thank you for your response!
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.
Thanks again for your help. Appreciated.
Cheers
Hello,
thank you for your information.
I am just wondering whether I can name a sheet tab name as a cell value in a different sheet?
Cheers.
Tina
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:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$A$1” Then Sheet1.Name = Range(“A1”).Value
End Sub
If cell A1 gets its value changed as the result of a formula, you’d use the Calculate event:
Private Sub Worksheet_Calculate()
Sheet1.Name = Range(“A1”).Value
End Sub
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.
Thank you, Tom.
In my case, I want to automatically change the name of Sheet 2 to a child ID inputted in Sheet 1 (say, B2).
Do I open the VBE and select Sheet 1 and then put in the Change Event code? Like:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$B$2” Then Sheet2.Name = Range(“B2”).Value
End Sub
When I did so, it does not seem to change anything. Excel said ‘ambiguous title’ or something, but the warning does not show up anymore.
I need to protect sheet 2. Does it affect anything?
I am such a VBA beginner. Thanks for your help.
Tina
Given the scenario you outline, the code would go into the Sheet1 module because it is cell B2 on Sheet1 that is being inputted and hence changed.
The code would be (assuming the VBA object code name for the worksheet whose tab name is currently Sheet2 but which you want to change), then in the Sheet1 module goes this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = “$B$2” Then Sheet2.Name = Target.Value
End Sub
I did what you said but the Excel says ‘Compile error: Ambiguous name detected: Worksheet_Change.’
It is probably because I have other code above starting with the same first line
Private Sub Worksheet_Change(ByVal Target As Range).
How do I solve that?
Even though I deleted my previous codes and typed in your codes, the Excel says ‘syntax error.’
Heaps of thanks
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.
How do I solve this issue?
Sorry for so many questions.
Thanks in advance
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
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
And that does it! Thank you for your help. Greatly appreciated!
Hi Tom, I used your formula, but when I close the Excel file and open it again the code disappears, what should I do?
Make sure you save your file as Macro enabled, with the .xlsm extension.
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.
Hi Tom,
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?
Thank you!
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?
[code]
Private Sub Worksheet_Change(ByVal Target As Range)
‘Specify the target cell whose entry shall be the sheet tab name.
If Target.Column A 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
End Sub
[/code]
Oh also, I tried it with Target.Column 1 like you originally said, but that didn’t work so I was experimenting with “A”. Thank you!
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.
[code]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 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
End Sub
[/code]
Thank you very much for your code!
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!
Hi All,
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).
If a user selecting the “Asia Pacific” region from drop down (in cell C5 of cover tab) ends up selecting 8 markets in cells B5:B12 (via dynamic data validation returning APAC markets) then all sheet names (from sheetcode names 3-10) should now reflect the new names i.e. Argentina (tab name for sheetcode “Sheet3”) now becomes lets say “Japan” (new value in cell B5 of cover tab); Brazil (tab name for sheetcode “Sheet4”) now becomes lets say “Australia” (new value in cell B6 of cover tab); Colombia (tab name for sheetcode “Sheet5”) now becomes lets say “Hong Kong” (new value in cell B7 of cover tab); Mexico (tab name for sheetcode “Sheet6”) now becomes lets say “Singapore” (new value in cell B8 of cover tab); Sheet7 (tab name for sheetcode “Sheet7”) now becomes lets say “Taiwan” (new value in cell B9 of cover tab); Sheet8 (tab name for sheetcode “Sheet8”) now becomes lets say “Thailand” (new value in cell B10 of cover tab); Sheet9 (tab name for sheetcode “Sheet9”) now becomes lets say “Phillipines” (new value in cell B11 of cover tab); Sheet10 (tab name for sheetcode “Sheet10”) now becomes lets say “India” (new value in cell B12 of cover tab); so basically the previous 4 market names (& remaining similar tab/sheetcode name) now get renamed with 8 market names (for all sheetcodes “Sheet3” to “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.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim celltxt As String
celltxt = ActiveSheet.Range(“C2”).Text
If InStr(1, celltxt, “Central New Story”) Or InStr(1, celltxt, “Green”) Or InStr(1, celltxt, “West New Story”) Or InStr(1, celltxt, “Northeast New Story”) Or InStr(1, celltxt, “Southeast New Story”) Then
Columns(“h:i”).EntireColumn.Hidden = False
Else
Columns(“h:i”).EntireColumn.Hidden = True
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
‘If the target cell is empty (contents cleared) do not change the sheet name.
If IsEmpty(Target) Then Exit Sub
d = ActiveWorkbook.ActiveSheet.Name
Sheets(d).Name = Range(“C4”).Value
End Sub
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
Maybe you can use is empty in that case, example
If isempty(target) = true then
Activesheet.name = Something
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.
Hello,
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?
How do the names on Sheet1 A2:A31 get into those cells?
I added a drop down list from a master list of names using data validation.
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?
I can get rid of the list if that helps and simply type the employee name into the cell each day as needed.
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.
Replace the original code with this revision:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intRow%
For intRow = 2 To 32
With Cells(intRow, 1)
Select Case Len(.Value)
Case Is > 1: Sheets(intRow).Name = .Value
Case 0: Sheets(intRow).Name = "Sheet" & intRow
End Select
End With
Next intRow
End Sub
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!!!
Thanks for the follow-up.
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”
Anyone that could help me ?
Thanks in advance.
Hi, I need your help. I want to name the sheet2 to be equal to the cell(A2) on sheet1, and sheet3 to be equal to cell(A3) on sheet 1, and so on. How can I do this??
Hello Dale – –
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.
Thanks Tom, It worked and this is very helpful, however I can only run it once. If I change the value and try to run the code again, I get an error, “Run-time error “9”: Subscript out of range”
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
This is great.
Pivot extract’s sheet naming question…
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?
This sounds do-able, but just to clarify, are you saying that in no case will a pivot table occupy cell AA2 on any sheet?
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.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range(“B17”)
If Target = “” Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub
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?
I would be very grateful for any advice you could give.
Many thanks
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.
Tom
Hi Tom,
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?
Thanks again for the code, it has been very usegul!
Well, let’s see if I understand what you are saying.
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.
Hey Tom I need a simple code for naming a tab on sheet 2, from a cell A13 on sheet 1.
is there one.
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.
All you need to do is make two small changes to the original procedure I posted for this tutorial.
Small change #1:
The actual first line of code is
If Target.Address <> "$A$1" Then Exit SubChange that to
If Target.Address <> "$A$13" Then Exit SubSmall change #2:
The eighth line of code above the
End Subline isActiveSheet.Name = strSheetNameChange that to
Sheet2.Name = strSheetNameThat revised code then goes into the Sheet1 (tab name TIE IN LIST) worksheet module.
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.
Hi Tom!
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
James and I seam to have a similar problem. the code works fine for one tab, how do we add additional tabs. in my case, I’m just going down the line A13, A14 and so on.
Tom,
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?
Thanks!
Lee
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!
Thanks dear. It’s awesome.
Thank you!
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
Thank you, Eric!
Hi Tom,
I have A column B15 to B34 in Sheet1
I would like to name my 20 sheets in same workbook based on this column (B15:B34). How can I do it automatically?
Thanks
Hello Allan – –
Assuming your sheet with the 20 cells from B15 to B34 is the active sheet
and
you already have 20 worksheets in your workbook
then this will do what you ask:
Sub NaymeSheetz()Dim i%
For i = 15 To 34
Sheets(i - 14).Name = Cells(i, 2).Value
Next i
End Sub
If there is another aspect to your question that is contrary to my above assumptions, please post back with more detail about what your situation is.
I replayed to your email but I couldn’t see it now. I will wait may will appear later.
Seems that this should work as a macro instead of as a selection change event that you posted, assuming this is a one-time thing on your first sheet that has the list of sheet names in B15:B34.
You did not say what your sheet tab name is that is the first sheet, so I will assume it is Sheet1. Delete that other Selection event code in your workbook in try this:
Sub NaymeSheetz()
Dim i%
For i = 15 To 34
Sheets(i – 13).Name = Worksheets(“Sheet1”).Cells(i, 2).Value
Next i
End Sub
Thanks Tom.
How to delete other Selection event code in my workbook.
what is the wrong with this?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet2.Name = Sheet1.Range(“B15”)
Sheet3.Name = Sheet1.Range(“B16”)
Sheet4.Name = Sheet1.Range(“B17”)
Sheet5.Name = Sheet1.Range(“B18”)
Sheet6.Name = Sheet1.Range(“B19”)
Sheet7.Name = Sheet1.Range(“B20”)
Sheet8.Name = Sheet1.Range(“B21”)
Sheet9.Name = Sheet1.Range(“B22”)
Sheet10.Name = Sheet1.Range(“B23”)
Sheet11.Name = Sheet1.Range(“B24”)
Sheet12.Name = Sheet1.Range(“B25”)
Sheet14.Name = Sheet1.Range(“B27”)
Sheet15.Name = Sheet1.Range(“B28”)
Sheet16.Name = Sheet1.Range(“B29”)
Sheet17.Name = Sheet1.Range(“B30”)
Sheet18.Name = Sheet1.Range(“B31”)
Sheet19.Name = Sheet1.Range(“B32”)
Sheet20.Name = Sheet1.Range(“B33”)
Sheet21.Name = Sheet1.Range(“B34”)
End Sub
You delete that code by going into the worksheet module where the code is, selecting the code and hitting the Delete key.
I tried your code but didn’t work
“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.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet2.Name = Sheet1.Range(“B15”)
Sheet3.Name = Sheet1.Range(“B16”)
Sheet4.Name = Sheet1.Range(“B17”)
Sheet5.Name = Sheet1.Range(“B18”)
Sheet6.Name = Sheet1.Range(“B19”)
Sheet7.Name = Sheet1.Range(“B20”)
Sheet8.Name = Sheet1.Range(“B21”)
Sheet9.Name = Sheet1.Range(“B22”)
Sheet10.Name = Sheet1.Range(“B23”)
Sheet11.Name = Sheet1.Range(“B24”)
Sheet12.Name = Sheet1.Range(“B25”)
Sheet14.Name = Sheet1.Range(“B27”)
Sheet15.Name = Sheet1.Range(“B28”)
Sheet16.Name = Sheet1.Range(“B29”)
Sheet17.Name = Sheet1.Range(“B30”)
Sheet18.Name = Sheet1.Range(“B31”)
Sheet19.Name = Sheet1.Range(“B32”)
Sheet20.Name = Sheet1.Range(“B33”)
Sheet21.Name = Sheet1.Range(“B34”)
End Sub
Thanks Tom,
Was my mistake, I put it the stand. module.
Thanks for the follow-up.
Hello,
I don’t know if my previous message was sent but it work right after i sent it… Although i have another question :
How could you make the script so that it would cut the name if it is more than 31 characters and it would replace any illegal character by a dash (-) ?
Thanks a lot
A couple things…
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.
Hello Mark, a couple things – –
(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
You can do this with a macro to loop through all the cells in A1:A20 instead of the Change event in my example. Post back if you need help with that.
if you can please help me with the code it would be great. I am very new to VB scripting
Here is my code (really your code :))
—————————-
Private Sub Worksheet_Change(ByVal Target As Range)
‘Specify the target cell whose entry shall be the sheet tab name.
If Intersect(Target, Range(“A2:A20”)) 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
Select Case Target.Address
Case “$A$2”: Sheet1.Name = strSheetName
Case “$A$3”: Sheet3.Name = strSheetName
Case “$A$4”: Sheet4.Name = strSheetName
Case “$A$5”: Sheet5.Name = strSheetName
Case “$A$6”: Sheet6.Name = strSheetName
Case “$A$7”: Sheet7.Name = strSheetName
Case “$A$8”: Sheet8.Name = strSheetName
Case “$A$9”: Sheet9.Name = strSheetName
Case “$A$10”: Sheet10.Name = strSheetName
Case “$A$11”: Sheet11.Name = strSheetName
Case “$A$12”: Sheet12.Name = strSheetName
Case “$A$13”: Sheet13.Name = strSheetName
Case “$A$14”: Sheet14.Name = strSheetName
Case “$A$15”: Sheet15.Name = strSheetName
Case “$A$16”: Sheet16.Name = strSheetName
Case “$A$17”: Sheet17.Name = strSheetName
Case “$A$18”: Sheet18.Name = strSheetName
Case “$A$19”: Sheet19.Name = strSheetName
Case “$A$20”: Sheet20.Name = strSheetName
End Select
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
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
I hope this is more clear
thank you
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.
Ok thanks you ill post there. I think Ill do some cleanup based on your comments and re-post there
thanks again
Hi Tom,
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?
Thanks
Private Sub Worksheet_Calculate()
With Range(“C13”)
If Len(.Value) = 0 Or Len(.Value) > 31 Then Exit Sub
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(.Text, (IllegalCharacter(i))) > 0 Then
MsgBox “The trade name contains a character that violates sheet naming rules.” & vbCrLf & _
“Reenter the name without the ”” & IllegalCharacter(i) & “” character.”, _
48, “Not a possible sheet name !!”
Exit Sub
End If
Next i
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
End With
End Sub
So sorry, answered my own silly question…
ElseIf IsEmpty(Target) Then Exit Sub
hanks for your followup. I’m traveling this week and was just catching up on my messages when I saw this today. Cool, looks like you have this solved.
Hi,
I am hoping you will be able to help me 🙂 I’m brand new to VBA and have taught myself the little pieces I do know. I recently built the following Macro, that duplicates a “master” tab when a specific cell is populated with specific text:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“A5:A200”)) Is Nothing Then
Select Case Range(“A5”)
Case “ACUM”: CopyRename
Case “AFTR”: CopyRename
Case “ALG”: CopyRename
Case “ALGT”: CopyRename
Case “ALMT”: CopyRename
Case “AMB”: CopyRename
Case “AUTB”: CopyRename
Case “BASE”: CopyRename
Case “BLD”: CopyRename
Case “CDIP”: CopyRename
Case “CDOP”: CopyRename
Case “CDO1”: CopyRename
Case “CDRR”: CopyRename
Case “CHIR”: CopyRename
Case “CMPL”: CopyRename
Case “COIN”: CopyRename
Case “CRCS”: CopyRename
Case “DED”: CopyRename
Case “DED1”: CopyRename
Case “DED2”: CopyRename
Case “DED3”: CopyRename
Case “DED4”: CopyRename
Case “DED5”: CopyRename
Case “DEN”: CopyRename
Case “DMEB”: CopyRename
Case “DMES”: CopyRename
Case “DPP”: CopyRename
Case “DUMY”: CopyRename
Case “EMER”: CopyRename
Case “EMPH”: CopyRename
Case “EPOT”: CopyRename
Case “EXAB”: CopyRename
Case “FAM”: CopyRename
Case “GRP”: CopyRename
Case “GYN”: CopyRename
Case “HC”: CopyRename
Case “HCSS”: CopyRename
Case “HEAR”: CopyRename
Case “HH”: CopyRename
Case “HLTH”: CopyRename
Case “HOOP”: CopyRename
Case “HOS2”: CopyRename
Case “HOSP”: CopyRename
Case “HRA”: CopyRename
Case “HRA2”: CopyRename
Case “INFT”: CopyRename
Case “LAB”: CopyRename
Case “LTM”: CopyRename
Case “MHBI”: CopyRename
Case “MHBO”: CopyRename
Case “MHIP”: CopyRename
Case “MHOP”: CopyRename
Case “OPM”: CopyRename
Case “OPM1”: CopyRename
Case “OPM2”: CopyRename
Case “OPM3”: CopyRename
Case “OPM4”: CopyRename
Case “OPM5”: CopyRename
Case “OPM6”: CopyRename
Case “OPT”: CopyRename
Case “OVC”: CopyRename
Case “OVC2”: CopyRename
Case “OVC3”: CopyRename
Case “OVC4”: CopyRename
Case “OVC5”: CopyRename
Case “OVC6”: CopyRename
Case “OVC7”: CopyRename
Case “OVC8”: CopyRename
Case “OXYG”: CopyRename
Case “PP”: CopyRename
Case “PNMT”: CopyRename
Case “ORTH”: CopyRename
Case “PV”: CopyRename
Case “REHB”: CopyRename
Case “REOP”: CopyRename
Case “RX01”: CopyRename
Case “RX02”: CopyRename
Case “RX03”: CopyRename
Case “RX04”: CopyRename
Case “RX05”: CopyRename
Case “RX06”: CopyRename
Case “SGOP”: CopyRename
Case “SNF”: CopyRename
Case “SNKR”: CopyRename
Case “SPVC”: CopyRename
Case “STU”: CopyRename
Case “TABS”: CopyRename
Case “TRAN”: CopyRename
Case “TRGN”: CopyRename
Case “TRVL”: CopyRename
Case “WLCH”: CopyRename
Case “XPRO”: CopyRename
Case “XRAY”: CopyRename
Case “VADD”: CopyRename
Case “DIAL”: CopyRename
Case “EFED”: CopyRename
Case “EP2R”: CopyRename
Case “EPO”: CopyRename
Case “FRST”: CopyRename
Case “GYN1”: CopyRename
Case “HEA1”: CopyRename
Case “KPCV”: CopyRename
Case “MDCV”: CopyRename
Case “MDEX”: CopyRename
Case “MHO1”: CopyRename
Case “REO1”: CopyRename
Case “REXC”: CopyRename
Case “RHLF”: CopyRename
Case “RHLP”: CopyRename
Case “RHPQ”: CopyRename
Case “RHSC”: CopyRename
Case “RKPO”: CopyRename
Case “RTHA”: CopyRename
Case “RX”: CopyRename
Case “SPV1”: CopyRename
End Select
End If
If Not Intersect(Target, Range(“J5:J200”)) Is Nothing Then
Select Case Range(“J5”)
Case “ACUM”: CopyRename
Case “AFTR”: CopyRename
Case “ALG”: CopyRename
Case “ALGT”: CopyRename
Case “ALMT”: CopyRename
Case “AMB”: CopyRename
Case “AUTB”: CopyRename
Case “BASE”: CopyRename
Case “BLD”: CopyRename
Case “CDIP”: CopyRename
Case “CDOP”: CopyRename
Case “CDO1”: CopyRename
Case “CDRR”: CopyRename
Case “CHIR”: CopyRename
Case “CMPL”: CopyRename
Case “COIN”: CopyRename
Case “CRCS”: CopyRename
Case “DED”: CopyRename
Case “DED1”: CopyRename
Case “DED2”: CopyRename
Case “DED3”: CopyRename
Case “DED4”: CopyRename
Case “DED5”: CopyRename
Case “DEN”: CopyRename
Case “DMEB”: CopyRename
Case “DMES”: CopyRename
Case “DPP”: CopyRename
Case “DUMY”: CopyRename
Case “EMER”: CopyRename
Case “EMPH”: CopyRename
Case “EPOT”: CopyRename
Case “EXAB”: CopyRename
Case “FAM”: CopyRename
Case “GRP”: CopyRename
Case “GYN”: CopyRename
Case “HC”: CopyRename
Case “HCSS”: CopyRename
Case “HEAR”: CopyRename
Case “HH”: CopyRename
Case “HLTH”: CopyRename
Case “HOOP”: CopyRename
Case “HOS2”: CopyRename
Case “HOSP”: CopyRename
Case “HRA”: CopyRename
Case “HRA2”: CopyRename
Case “INFT”: CopyRename
Case “LAB”: CopyRename
Case “LTM”: CopyRename
Case “MHBI”: CopyRename
Case “MHBO”: CopyRename
Case “MHIP”: CopyRename
Case “MHOP”: CopyRename
Case “OPM”: CopyRename
Case “OPM1”: CopyRename
Case “OPM2”: CopyRename
Case “OPM3”: CopyRename
Case “OPM4”: CopyRename
Case “OPM5”: CopyRename
Case “OPM6”: CopyRename
Case “OPT”: CopyRename
Case “OVC”: CopyRename
Case “OVC2”: CopyRename
Case “OVC3”: CopyRename
Case “OVC4”: CopyRename
Case “OVC5”: CopyRename
Case “OVC6”: CopyRename
Case “OVC7”: CopyRename
Case “OVC8”: CopyRename
Case “OXYG”: CopyRename
Case “PP”: CopyRename
Case “PNMT”: CopyRename
Case “ORTH”: CopyRename
Case “PV”: CopyRename
Case “REHB”: CopyRename
Case “REOP”: CopyRename
Case “RX01”: CopyRename
Case “RX02”: CopyRename
Case “RX03”: CopyRename
Case “RX04”: CopyRename
Case “RX05”: CopyRename
Case “RX06”: CopyRename
Case “SGOP”: CopyRename
Case “SNF”: CopyRename
Case “SNKR”: CopyRename
Case “SPVC”: CopyRename
Case “STU”: CopyRename
Case “TABS”: CopyRename
Case “TRAN”: CopyRename
Case “TRGN”: CopyRename
Case “TRVL”: CopyRename
Case “WLCH”: CopyRename
Case “XPRO”: CopyRename
Case “XRAY”: CopyRename
Case “VADD”: CopyRename
Case “DIAL”: CopyRename
Case “EFED”: CopyRename
Case “EP2R”: CopyRename
Case “EPO”: CopyRename
Case “FRST”: CopyRename
Case “GYN1”: CopyRename
Case “HEA1”: CopyRename
Case “KPCV”: CopyRename
Case “MDCV”: CopyRename
Case “MDEX”: CopyRename
Case “MHO1”: CopyRename
Case “REO1”: CopyRename
Case “REXC”: CopyRename
Case “RHLF”: CopyRename
Case “RHLP”: CopyRename
Case “RHPQ”: CopyRename
Case “RHSC”: CopyRename
Case “RKPO”: CopyRename
Case “RTHA”: CopyRename
Case “RX”: CopyRename
Case “SPV1”: CopyRename
End Select
End If
End Sub
it works phenomenally! However, I am now trying to build a macro (similar to yours above I believe) that will auto-name the new tabs with the values that are concatenated with the current macro. (ie: Cell A5:A200 is what triggers the current macro, there are also values in B5:B200 and the values in A5:A200 are concatenated together in C5:C200) I want the new macro to use the values that are loaded in C5:C200 to be the names of the new tabs created when a value is populated in A5:A200.
Is there any way to do this?
Thank You,
Heather
An Example of the Concatenate Cells is: ALMT – B5
I have it set up to include a space in-between each value and includes a -. ALMT is what is in cell A5 and B5 is what is in cell C5.
I also forgot to mention that all of cells A5:A200 are values that are compiled via a drop down list to trigger the current macro.
Thank You So Much in advance for your help!
A lot of cases there. A few questions:
(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.
(2)
What exactly is “CopyRename”?
(3)
This drop down list that you mentioned, first, I assume it is data validation, but can you confirm that.
(4)
More importantly, are those items such as ACUM, AFTR and so on the ***ONLY*** values allowed in the cells.
(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.
Hi Tom, Thank you for the quick response!
(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.
See my reply to your follow-up comment.
Hi Tom
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?
Thanks
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.
Thanks for your quick reply in answer to your questions:
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
3 it doesn’t have any of the check functions your code has.
Sub RenameSheet()
Sheet10.Name = Sheet10.Range(“A1”)
Sheet12.Name = Sheet12.Range(“A1”)
Sheet13.Name = Sheet13.Range(“A1”)
Sheet14.Name = Sheet14.Range(“A1”)
Sheet15.Name = Sheet15.Range(“A1”)
Sheet16.Name = Sheet16.Range(“A1”)
Sheet17.Name = Sheet17.Range(“A1”)
Sheet18.Name = Sheet18.Range(“A1”)
Sheet19.Name = Sheet19.Range(“A1”)
Sheet20.Name = Sheet20.Range(“A1”)
Sheet21.Name = Sheet21.Range(“A1”)
Sheet22.Name = Sheet22.Range(“A1”)
Sheet23.Name = Sheet23.Range(“A1”)
Sheet24.Name = Sheet24.Range(“A1”)
Sheet25.Name = Sheet25.Range(“A1”)
Sheet26.Name = Sheet26.Range(“A1”)
Sheet27.Name = Sheet27.Range(“A1”)
Sheet28.Name = Sheet28.Range(“A1”)
Sheet29.Name = Sheet29.Range(“A1”)
Sheet30.Name = Sheet30.Range(“A1”)
End Sub
There is a loop recommendation I can make, but before that, is there a reason why you skip Sheet11 although there seems to be a space for it in your code. What is so special about Sheet11.
Sheet 11 was a master sheet that I created but its not a problem if the sheets need to be sequential I can move the data around if necessary.
Thanks
Keep it simple and use Sheet11 as you have Sheet10 and Sheets 12 to 30.
This should do what you want.
Sub ChangeSheetNames()Dim xCounter As Integer, strNewName As String
For xCounter = 10 To 30
With Sheets("Sheet" & xCounter)
strNewName = .Range("A1").Value
'Disallow if it is greater than 31 characters.
If Len(strNewName) > 31 Then
MsgBox "Worksheet names cannot be more than 31 characters." & vbCrLf & _
strNewName & " has " & Len(strNewName) & " characters.", _
48, "Keep it under 31 characters."
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(strNewName, (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 !!"
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 = 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
End With
Next xCounter
End Sub
Hi I must be missing something I’ve entered the below on the cover sheet I am using to populate the names of the other sheets. Do you think it’s an issue with “strNewName = .Range(“G15:G35″).Value”
Sub ChangeSheetNames()
Dim xCounter As Integer, strNewName As String
For xCounter = 10 To 30
With Sheets(“Sheet” & xCounter)
strNewName = .Range(“G15:G35”).Value
‘Disallow if it is greater than 31 characters.
If Len(strNewName) > 31 Then
MsgBox “Worksheet names cannot be more than 31 characters.” & vbCrLf & _
strNewName & ” has ” & Len(strNewName) & ” characters.”, _
48, “Keep it under 31 characters.”
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(strNewName, (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 !!”
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 = 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
End With
Next xCounter
End Sub
Thanks again
Jason
Change this
strNewName = .Range(“G15:G35”).Value
to this
strNewName = .Cells(xCounter + 5, 7).Value
Thanks Tom I get a run-time error 9 Subscript out of range. I think there’s also a problem with this line
With Sheets(“Sheet” & xCounter) I think “sheet” is referencing a sheet named “sheet” rather than the VB sheet number. I have tried removing the “” to no avail any thoughts?
Ta
I assumed that you were relying in the worksheets’ default names (and not index numbers) starting with the sheets you care about as the default Sheet10, Sheet11, and so on up to Sheet30.
Guessing without seeing your workbook, but maybe all that needs to be done now is to change this
With Sheets(“Sheet” & xCounter)
to this
With Sheets(xCounter)
which will deal with those worksheets in their indexed position of tenth through and including thirtieth.
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
Sub ChangeSheetNames()
Dim xCounter As Integer, strNewName As String
For xCounter = 10 To 30
With Sheets(xCounter)
strNewName = .Range(“A1”).Value
‘Disallow if it is greater than 31 characters.
If Len(strNewName) > 31 Then
MsgBox “Worksheet names cannot be more than 31 characters.” & vbCrLf & _
strNewName & ” has ” & Len(strNewName) & ” characters.”, _
48, “Keep it under 31 characters.”
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(strNewName, (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 !!”
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 = 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
End With
Next xCounter
End Sub
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?
Would this be easier to achieve?
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’d appreciate any insights you are willing to offer!
Hello, Esther – –
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.
Oh, forgot to mention, I copied your original code above and only changed the on $A$1 reference to $C$2. Everything else is the same.
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.
Tom, when I View Source from Sheet1, I do not see the Worksheet_Change code. I put the code on the This Workbook item in the VBA Console. Sheet1 is blank, as are the other sheets.
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.
So, I need to copy it to all 26 worksheets? I want all the worksheets to update when I change the date on the first worksheet.
Sorry, it does say View Code. My mistake.
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!
Format your C2 cell as Text. Then enter in a date with characters that do not violate sheet naming rules. All those characters, including the forward slash, are included in my code.
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!
I encounter Run time error 424 Object required with the first code.
If Target.Address “$b$4” Then
Is there a way to unmerge range then apply these codes?
It can probably be done but you would need to explain more details.
You left out the = so it should be
If Target.Address = “$b$4” ThenI’m trying to use this code to change the tabs on sheets 6-20 automatically to match cells on their respective sheets. However these cells on sheets 6-20 are themselves referencing cells on sheet 1 (ie =’sheet1′!e13). This code doesn’t appear to recognize changes to the cells on sheet 1 because they’re looking for changes in the cell in their respective sheets, which will always remain =’sheet1′!E13.
Is it possible to reference sheet 1 for the tabs names of sheets 6-20?
The short answer is yes.
The longer answer is, you can use the Workbook level Calculate event (so you don’t need to install 15 separate event codes into sheets 6-20’s worksheet modules) for sheets 6-20 to monitor whichever cell address that holds the changing sheet name. You did not say what the cell address is, or what they are if each sheet 6-20 has a different cell referring to Sheet1. Or, you can monitor the range in Sheet1 where (presumably) you have listed 15 cells that each contain names corresponding to sheets 6-20, with a Change event that changes the sheet tab names for those respective cells. The only issue with that is that the worksheet object codename would be needed to associate which cell in Sheet1 (you also did not say what range location is in Sheet1 except for cell E13) corresponds to which sheet tab.
What you want to do is do-able, it just depends on which method or tactic you want to use to go about it.
Thanks Tom! Sorry for the vague nature of my question. I just started learning all of this, so I’m not very well versed in the language and necessary details to convey the full scope of my inquiry. I’ve changed the references a little bit below to match the changes i’ve made to my workbook. Hopefully this is more clear.
What I want is for whatever is typed into cells B12:B41 in sheet1 to change the names of worksheet 6-35 respectively. So each worksheet would have a different name corresponding to one of the cells in that range. I think this matches your second method. Can I just use your above code and change the target cell to one on sheet 1? What’s the syntax change involved? Or is there a better way to do that?
No worries – – everyone was a beginner once.
OK, so it is not 15 sheets, it is 30 sheets. That in and of itself is no problem.
To me, the second method, which is the direction you are thinking of, would be the better of the two, IF (notice the caps for emphasis) you can do a once-only bit of housekeeping, UNLESS there will NEVER be a case where the order (in VBA speak, the Index number) of the worksheets changes. So you need to tell me if it is IMPOSSIBLE that the sheet tabs’ order from left to right will NEVER change. The chances of that being the case are the same as a vaccine ready to cure Covid by midnight tonight. Probably won’t happen. But I am asking anyway, so is it yes (not likely) that the order of the sheet tabs will always and forever remain constant, or no, that a possibility exists that the worksheets as they are sequenced now will change their order sooner or later.
The intent is that they will remain the same always. Only thing that should change is the content of the reference cell
This will do what you want.
Copy it to your clipboard.
Right-click onto your Sheet1 tab.
Left click onto View Code.
Paste the code into the large white area that is Sheet1’s worksheet module.
Press Alt+Q to close the Visual Basic Editor.
Works when I tested it.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B12:B41")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You cannot clear the contents or a cell." & vbCrLf & "You can only edit the contents.", 16, "Can't do that."
Exit Sub
End If
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.Value = "XXXXX"
Application.EnableEvents = True
Exit Sub
End If
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.Value = "XXXXXX"
Application.EnableEvents = True
Exit Sub
End If
Next i
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 bln = False Then
Dim xRow&
xRow = Target.Row
Sheets(xRow - 6).Name = Target.Value
Else
MsgBox "You already named a sheet " & strSheetName & "." & vbCrLf & _
"Enter a unique name for this sheet."
Application.EnableEvents = False
Target.Value = "XXXXXXX"
Application.EnableEvents = True
End If
End Sub
That was perfect. Appreciate the help!
Hi Tom,
Hope you are well.
I have used the code in the post from October 23, 2013 for changes to multiple sheets names from a single sheet. This works well. However is there a way to make it so the reference cells on sheet 1 can move.
they are currently A22 to A27 inclusive and i would like to be able to move some of the cells to M22 to M27 incluive for example.
Please see my code below for reference:
Private Sub Worksheet_Change(ByVal Target As Range)
‘Specify the target cell whose entry shall be the sheet tab name..
If Intersect(Target, Range(“A22,A23,A24,A25,A26,A27”)) 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
Select Case Target.Address
Case “$A$22”: Sheet2.Name = strSheetName
Case “$A$23”: Sheet3.Name = strSheetName
Case “$A$24”: Sheet4.Name = strSheetName
Case “$A$25”: Sheet5.Name = strSheetName
Case “$A$26”: Sheet6.Name = strSheetName
Case “$A$27”: Sheet7.Name = strSheetName
End Select
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
Many thanks in advance
Very best regards
Neil
Hello, Neil – –
Taking your question literally, yes you can move your target cells on Sheet1 from A22:A27 to M22:M27 with these quick changes to the code:
Change this line:
If Intersect(Target, Range("A22,A23,A24,A25,A26,A27")) Is Nothing Or Target.Cells.Count > 1 Then Exit Subto this:
If Intersect(Target, Range("M22:M27")) Is Nothing Or Target.Cells.Count > 1 Then Exit Suband change your Case addresses from this:
Select Case Target.AddressCase "$A$22": Sheet2.Name = strSheetName
Case "$A$23": Sheet3.Name = strSheetName
Case "$A$24": Sheet4.Name = strSheetName
Case "$A$25": Sheet5.Name = strSheetName
Case "$A$26": Sheet6.Name = strSheetName
Case "$A$27": Sheet7.Name = strSheetName
End Select
to this:
Select Case Target.AddressCase "$M$22": Sheet2.Name = strSheetName
Case "$M$23": Sheet3.Name = strSheetName
Case "$M$24": Sheet4.Name = strSheetName
Case "$M$25": Sheet5.Name = strSheetName
Case "$M$26": Sheet6.Name = strSheetName
Case "$M$27": Sheet7.Name = strSheetName
End Select
Assuming your associated sheet object codenames are the same, this should do what you want.
Hi Tom,
Many thanks for your help.
It is greatly appreciated.
Looking at the way I want this to work is the contents of A22 will remain in the same place, but i may want to move the contents of the A23 to M22, this way i can do a comparison of costs between A22 and M22.
I have actually used code so i can paste the contents of the cell from A23 in to M22, A24 in to M23 and so on using the following.
Case “$A$22”: Sheet2.Name = strSheetName
Case “$A$23”: Sheet3.Name = strSheetName
Case “$A$24”: Sheet4.Name = strSheetName
Case “$A$25”: Sheet5.Name = strSheetName
Case “$A$26”: Sheet6.Name = strSheetName
Case “$A$27”: Sheet7.Name = strSheetName
Case “$M$22”: Sheet3.Name = strSheetName
Case “$M$23”: Sheet4.Name = strSheetName
Case “$M$24”: Sheet5.Name = strSheetName
Case “$M$25”: Sheet6.Name = strSheetName
Case “$M$26”: Sheet7.Name = strSheetName
Thanks again for your help.
Very best regards
You lost me.
Bottom line:
What cells do you care about entering something in, and having a sheet tab name change to what you entered into that cell.
Hello Tom,
How can I use your formula and display a prompt vice existing the macro when characters are 0 or greater than 31?
If greater than 31, can you make it that it only renames with the first 25 characters of the cell?
Also, to avoid duplicate name errors, how can you the sheet be renamed using two cells, vice just one.
Here is what I have, and is not working.
Private Sub Worksheet_Calculate()
With Range(“B3”)
If Len(.Value) = 0 Then
MsgBox “Worksheet names cannot be 0 characters.” & vbCrLf & _
Target.Value & ” has ” & Len(Target.Value) & ” characters.”, _
48, “Rename manually.”
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
‘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
Thanks, Tom for getting me here to this page. My issue is that I want to name the pages on the bottom of my spreadsheet to reflect the names of the students that are in my class. I have a list of up to 30 students and each student is given their very own excel page where I keep notes and observations of that student throughout the entire training period. I do not know how to rename the page tabs at the bottom of the spreadsheet to reflect the names of each student. I sent you some screenshots of the worksheet. part of my problem is that this spreadsheet is kind of a house that jack built and too many people have added to it over the years and they won’t let me make a new one, so ‘i am trying to doctor it up. Can you please help. Thank you so much!
In a previous email I asked you for the range of cells on the landing page where each of the 3 columns are. I am still waiting for you to tell me that.
What range is the list of 30 Specialist numbers.
What range is the list of 30 peoples names.
What range is the list of 30 sheet codenames that correspond to the list of 30 Specialist names.
Hi Tom,
What do i need to change in the code, if I want to place it as a sub in one of the modules?
I’ve another sub that is called/executed before this one, that changes the values of the target cells – ie “A1” in the first example.
I’ve got the first sub running and working, but when it calls this code it errors with “Runtime error 424” and in debugging it highlights this line:
“If Intersect(Target, Range(“Timer!C2:I2,Timer!C21:I21″)) Is Nothing Or Target.Cells.Count > 1 Then”
Thank you for time and effort.
Copy of the complete code:
Sub arknavneskift()
‘Ændrer arknavnene til de valgte datoer
‘Specify the target cell whose entry shall be the sheet tab name.
If Intersect(Target, Range(“Timer!C2:I2,Timer!C21:I21”)) 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 “Arknavne kan ikke være længere end 31 tegn.” & vbCrLf & _
Target.Value & ” er ” & Len(Target.Value) & ” tegn.”, _
48, “Hold det under 32 tegn, kontroller datoerne i kalender arket.”
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 “Du har brugt et tegn, som excel ikke vil acceptere i arknavne.” & vbCrLf & _
“Vælg et navn uden ”” & IllegalCharacter(i) & “” tegnet.”, _
48, “Dette er ikke et gyldigt ark navn!”
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 = Format(Date, “dd-mm”)
‘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
Select Case Target.Address
Case “Timer!$C$2”: Ark4.Name = strSheetName
Case “Timer!$D$2”: Ark5.Name = strSheetName
Case “Timer!$E$2”: Ark6.Name = strSheetName
Case “Timer!$F$2”: Ark7.Name = strSheetName
Case “Timer!$G$2”: Ark8.Name = strSheetName
Case “Timer!$H$2”: Ark9.Name = strSheetName
Case “Timer!$I$2”: Ark10.Name = strSheetName
Case “Timer!$C$21”: Ark11.Name = strSheetName
Case “Timer!$D$21”: Ark12.Name = strSheetName
Case “Timer!$E$21”: Ark13.Name = strSheetName
Case “Timer!$F$21”: Ark14.Name = strSheetName
Case “Timer!$G$21”: Ark15.Name = strSheetName
Case “Timer!$H$21”: Ark16.Name = strSheetName
Case “Timer!$I$21”: Ark17.Name = strSheetName
End Select
Else
MsgBox “Der er allerede et ark med navnet ” & strSheetName & “.” & vbCrLf & _
“Vælg et unikt navn til dette ark.”
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
‘https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-name-your-sheet-tab-same-as-cell-value/
End Sub
One question, is, or is not the worksheet named Timer the active sheet when this code executes.
Hey Tom
I’ve read this post and I’m trying to get your code work to my needs.
I have a workbook with sheets that has fixed names and a number of sheets which should be able to change sheetname based on a cellreference in the sheet named “Employees” (Sheet5). This sheet name is fixed. The range of sheetnames are in AT5:AT47 and will stay there. They are based on concatenated cellreferences in AR5:AR47 (formula). All sheetnames has the syntax “Number – Name” eg. “1 – James”. When a change is made, in essences, only the name of the employer will change eg. “1 – Peter”. From the beginning alle sheets will have a name, some though without a name since there is no employer at this point (tab name eg. “5 – “). Also the sheets that should be able to change names will be protected with password (same on all sheets):
But the sheet codename is not subsequent in the workbook, as you can see in the code below due to the fact, that some sheets not was there from the start of the workbook.
The situation now is that if a change a employer name on the sheet “Employees”, the concatenated value in AR** is changing as well the value in the range AT5:AT47, but the tab name isn’t changing.
Can you help me with this issue.
Thx in advance for your help.
Private Sub Worksheet_Calculate(ByVal Target As Range)
‘Specify the target cell whose entry shall be the sheet tab name.
If (Target.Range(“AT4:AT47”)) 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
Select Case Target.Address
Case “$AT$5”: Ark27.Name = strSheetName
Case “$AT$6”: Ark28.Name = strSheetName
Case “$AT$7”: Ark29.Name = strSheetName
Case “$AT$8”: Ark30.Name = strSheetName
Case “$AT$9”: Ark31.Name = strSheetName
Case “$AT$10”: Ark32.Name = strSheetName
Case “$AT$11”: Ark33.Name = strSheetName
Case “$AT$12”: Ark34.Name = strSheetName
Case “$AT$13”: Ark35.Name = strSheetName
Case “$AT$14”: Ark36.Name = strSheetName
Case “$AT$15”: Ark9.Name = strSheetName
Case “$AT$16”: Ark10.Name = strSheetName
Case “$AT$17”: Ark11.Name = strSheetName
Case “$AT$18”: Ark12.Name = strSheetName
Case “$AT$19”: Ark13.Name = strSheetName
Case “$AT$20”: Ark14.Name = strSheetName
Case “$AT$21”: Ark15.Name = strSheetName
Case “$AT$22”: Ark16.Name = strSheetName
Case “$AT$23”: Ark17.Name = strSheetName
Case “$AT$24”: Ark18.Name = strSheetName
Case “$AT$25”: Ark19.Name = strSheetName
Case “$AT$26”: Ark20.Name = strSheetName
Case “$AT$27”: Ark21.Name = strSheetName
Case “$AT$28”: Ark22.Name = strSheetName
Case “$AT$29”: Ark23.Name = strSheetName
Case “$AT$30”: Ark44.Name = strSheetName
Case “$AT$31”: Ark24.Name = strSheetName
Case “$AT$32”: Ark25.Name = strSheetName
Case “$AT$33”: Ark37.Name = strSheetName
Case “$AT$34”: Ark38.Name = strSheetName
Case “$AT$35”: Ark45.Name = strSheetName
Case “$AT$36”: Ark46.Name = strSheetName
Case “$AT$37”: Ark47.Name = strSheetName
Case “$AT$38”: Ark48.Name = strSheetName
Case “$AT$39”: Ark49.Name = strSheetName
Case “$AT$40”: Ark50.Name = strSheetName
Case “$AT$41”: Ark51.Name = strSheetName
Case “$AT$42”: Ark52.Name = strSheetName
Case “$AT$43”: Ark53.Name = strSheetName
Case “$AT$44”: Ark54.Name = strSheetName
Case “$AT$45”: Ark40.Name = strSheetName
Case “$AT$46”: Ark41.Name = strSheetName
Case “$AT$47”: Ark42.Name = strSheetName
End Select
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
Are Ark##’s sheet CodeNames or are they the concatenations. On a previous comment from another visitor, I gave an example of how to use the CodName property for situations like this. I understand the CodeNames are not in sequence but that does not matter. What does matter is unambiguously identifying each worksheet which the CodeName can do but the worksheet tabs cannot. It looks to me that if Ark## is not the CodeName, then the sheet tabs are not changing because you are not referencing them properly. My suggestion would be to add a column where the CodeNames for each sheet correspond to their worksheet tab counterparts in AT5:AT47. then you can refer to the CodeName in your code to change that respective worksheet tab name accordingly.
Hey Tom
Thanks for your answer. Ark## sholud be the codenames . When i look into “Microsoft Excel Objects” in the VBA editor, it’s listed like this:
Ark1 (Grundskema)
Ark10 (102 – 0)
Ark11(103 – Gitte E)
and so on
I already made the column you suggest prior to my post to check, that i referered correctly. I’m not sure i know have to implement your solution to the suggest.
/Jakob
I don’t want this to fall through the cracks because I have so much going on this week. I cannot totally wrap my arms around everything going on. Can you expound a bit to say:
• Which sheet has formulas in them that are changing?
• When one cell somewhere changes, I assume that means a different name will be applied to the respective worksheet tab.
• But is it always (always means always) that only one worksheet tab (meaning one worksheet cell) will change at a time, or can 2 or 5 or 30 cells change at once if for example you are copying and pasting many cells at the same time.
This is not a dig at you whatsoever, but something about this set-up just seems to be more complicated than it needs to be. But because I don’t have the workbook in front of me, I cannot know the entire situation and hence suspect that something more is going on or needs to be understood.
The formulas is on the sheet “Employees” (Sheet 5). When a name of an employee changes, yes the new name should be applied to the respective worksheet tab. There will be no copy/paste, so there will be only one change at the time. Although there can be some consecutive changing, if there is one or more new employees or old ones leaving, but the end user of this workbook will only change on cell at the time.
Too many back and forth variables to possibly be involved for a solution this way. Email me your workbook or a benign copy of it.
Hi Tom
Thank you for this, only thing is how would I adjust the code to include Sheet 1 where all the data will from different cells be used to create the sheet tabs?
Thank you
JetFusion
I read your question a few times but don’t understand.
You wrote:
where all the data will from different cells be used to create the sheet tabs
What are you saying? On Sheet1 you have something in cell A1, something in cell B1, and something in cell C1, and in cell D1 is a concatenation formula that is what a sheet tab name should be?
Think about how no one except you has seen your workbook, so a complete explanation is needed of what you are working with and what you want to achieve, so your situation can be understood.
Impressive stuff. Thanks for your work on it. This code is pretty much exactly what I’m attempting to do with a couple of exceptions.
1. If I clear the cell I’m looking at, AJ12, which we’ll call “Year”, I want it to rename the sheet to “YYYY Leave Calendar” and perform checks on that name. If there is already one of those then I want to add (i) to it where i is the next number available.
2. I’m not that great at VBA so I’m not sure, but it looks like it should clear the “Year” cell on checks that find one of the errors. It’s not doing that.
3. I’m also interested in not getting a messsage box for a rename attempt if the new value in “Year” is the same as it was. Say someone puts 2022 in and it renames the sheet “2022 Leave Calendar” and for some reason they edit the cell again but decide to put in the same year.
I’ve tried a few things but I can’t even make it do the “YYYY Leave Calendar” when I clear the cell for some reason. I have that working in another block of code that I commented out to try this fancy stuff.
Thanks for any help you can give!
Thanks. Not sure I follow everything you are saying.
When you say…
“I want it to rename the sheet to “YYYY Leave Calendar””
…do you literally means
“YYYY Leave Calendar”
…or do you mean for example
“2021 Leave Calendar”.
No idea what this means:
“If there is already one of those then I want to add (i) to it where i is the next number available.”
What are the “those” you refer to,
and
how do you add a letter character “i” to a number.
No idea what you mean by…
“I’m not that great at VBA so I’m not sure, but it looks like it should clear the “Year” cell on checks that find one of the errors. It’s not doing that.”
What is the Year cell,
and
what is an error you see
and
how is the error evidenced.
I won’t quote all of your number 3 but I don’t get the scenario you refer to.
I guess I didn’t explain as well as I thought. Things are always more clear when you’re writing and know what you are thinking than when you’re reading and trying to troubleshoot. My bad.
Yes if AJ12 is cleared I want it to rename the sheet “YYYY Leave Calendar” not insert a year as the year in AJ12 is now gone. Or just “Leave Calendar”. That’s easily changed if I want to have one or the other.
“Those” is a sheet with the name “YYYY Leave Calendar”. So if you have a sheet with an empty AJ12 so it’s named “YYYY Leave Calendar” already and you delete the year from AJ12 in another sheet it will try to rename the sheet to “YYYY Leave Calendar” and yell at you for having a duplicate like any other duplicate does with this code.
I think the (i) piece may be unnecessary but it would have been “YYYY Leave Calendar (1)”, “YYYY Leave Calendar (2)”… increasing each time you had a new sheet where you cleared AJ12. It was a concern I had about the Target.ClearContents which is one of the other questions you had that I talk about below. Basically I thought it might loop if I didn’t force a sequential rename on this empty AJ12 based sheet name.
The “Year” cell is AJ12 per 1.
Error was referring to the invalid sheet names and resulting message box that goes with it. I assume the “Target.ClearContents” line is supposed to clear the target cell after the invalid input is detected. It’s not clearing that cell. If it’s not doing that then nevermind on this one. It just seems like that’s what it was supposed to be doing. Again, not great at VBA so I wasn’t sure.
The message box that tells you there is a sheet with that name already. If I’m in a sheet and AJ12 is 2021 so its name is “2021 Leave Calendar” and I edit AJ12 but enter 2021 into it again I am informed that there is already a sheet with that name even though it’s the sheet I’m in and there is no conflict or issue with giving it that name again. That shouldn’t give me a message about a duplicate.
I tried to be more clear and less pronouny this time.
I’m trying to generate tab names from dates in the following format mm-dd-yyyy. All of the dates are seven days apart and in column B6:B15 of sheet1. There are going to be anywhere from 2 to 10+ dates (tab names) in that column. The formula to generate dates 2 to 10 is simply =B6+7, then =B7+7, then =B8+7 and so on. I have tried to modify your calculate code at the top of the page to work for my situation and I can’t get it to do anything. I have done just enough VBA coding to not know what I’m doing.
I did see the one post where you suggested to Tim the following for tab names based on sheet1 but all of the tab names were in column A. I don’t know how to modify it to work with column B.
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
Any help would be greatly appreciated.
I follow the crux of your question but you say some things that should not matter, which makes me wonder if they do matter.
For example, the formulas are irrelevant because they return dates. Maybe you want to change the sheet tabs each time you change a date. But if several dates are calculated at the same time you would not want the code to execute superfluously, so maybe you just need some other trigger.
And seeing that you used the Change event where formulas are concerned, maybe instead you should use the Calculate event. But that still leads to my previous comment about when and why your dates change.
Why do you have a + after 10 when you wrote
“There are going to be anywhere from 2 to 10+ dates”.
That could mean 2 to 350. is the range B6:B15 or B6 to something else.
How do you unambiguously determine which worksheets shall be named by the cell values.
Could it be that cell B6 contains a date but B7 does not and B8 does. How shall date-less cells in range B6:B15 be handled.
Probably other wonderings to also get cleared up but these are my first questions.
Hi, I’m using this to do a similiar thing, is it possible to alter this so as if cell A9 is empty it picks up the name from another cell (AA6)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = “A9” Then
On Error Resume Next
Me.Name = Target.Value
On Error GoTo 0
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$A$9" Then
On Error Resume Next
Me.Name = Target.Value
If Err.Number <> 0 Then
Err.Clear
Me.Name = Range("AA6").Value
End If
End If
End Sub