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

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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
138 comments on “Tom’s Tutorials For Excel: Name your sheet tab same as cell value.
  1. Biray Kocak says:

    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.

    • TomU says:

      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 Sub
      • Des K says:

        Hi 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.

      • Jim P says:

        When I copy this code into a module it doesn’t show up in my macro list. Thoughts?

        • Tom Urtis says:

          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.

  2. Troy says:

    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.

    • Tom Urtis says:

      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

      • Christian says:

        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

  3. jeffC says:

    Tom
    how can this code be modified to cover any changes of name in any worksheet in a workbook?

    • Tom Urtis says:

      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.

  4. Rossei says:

    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.

    • Tom Urtis says:

      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.

      • Rossei says:

        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.

  5. Tom Urtis says:

    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.

  6. Carl Witthoft says:

    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.

    • Tom Urtis says:

      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.

  7. Carl Witthoft says:

    Never mind — I missed the point that this sheet code autoruns when the cell is edited.

  8. Andy says:

    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

    .

    • Tom Urtis says:

      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

  9. Mark Gosnell says:

    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!.

  10. T. Reed says:

    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.

    • Tom Urtis says:

      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.

  11. Saleh says:

    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

  12. Mark Gosnell says:

    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.

  13. Ali Dean says:

    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

    • Tom Urtis says:

      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

  14. AJH says:

    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?

  15. miscurrie says:

    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…

    • Tom Urtis says:

      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.

      • Miscurrie says:

        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

        • Tom Urtis says:

          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.

  16. miscurrie says:

    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

  17. Tina says:

    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

    • Tom Urtis says:

      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.

      • Tina says:

        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

        • Tom Urtis says:

          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

          • Tina says:

            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

          • Tina says:

            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

          • Tom Urtis says:

            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.

  18. Bruno says:

    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

    • Tom Urtis says:

      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

      • Bruno says:

        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

        • Tom Urtis says:

          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

  19. Bruno says:

    And that does it! Thank you for your help. Greatly appreciated!

  20. Ryan B. says:

    Hi Tom, I used your formula, but when I close the Excel file and open it again the code disappears, what should I do?

  21. Brandon R. says:

    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.

  22. Jamie G says:

    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!

    • Tom Urtis says:

      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.

  23. Jamie G says:

    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]

  24. Jamie G says:

    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!

    • Tom Urtis says:

      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]

  25. Vincent says:

    Thank you very much for your code!

  26. dennis f says:

    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.

    • Tom Urtis says:

      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!

  27. Piyush Sharma says:

    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 ๐Ÿ™‚

    • Piyush Sharma says:

      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).

      • Tom Urtis says:

        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.

  28. Jason Myrick says:

    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

    • Tom Urtis says:

      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.

      • Jason Myrick says:

        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

  29. Julius Grandea says:

    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.

    • Tom Urtis says:

      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.

  30. Julius Grandea says:

    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.

  31. Julius Grandea says:

    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.

  32. Julius Grandea says:

    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.

    • Tom Urtis says:

      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.

  33. Tim says:

    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?

  34. Tim says:

    I added a drop down list from a master list of names using data validation.

    • Tom Urtis says:

      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?

  35. Tim says:

    I can get rid of the list if that helps and simply type the employee name into the cell each day as needed.

  36. Tim says:

    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.

    • Tom Urtis says:

      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

  37. Tim says:

    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.

    • Tom Urtis says:

      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

  38. Tim says:

    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.

  39. Tim says:

    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!!!

  40. Jesper says:

    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.

  41. dale says:

    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??

    • Tom Urtis says:

      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.

      • dale says:

        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”

        • Tom Urtis says:

          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

  42. Tammy says:

    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?

  43. Tracy Henderson says:

    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

    • Tom Urtis says:

      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

  44. Katie says:

    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!

    • Tom Urtis says:

      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.

  45. Alexander says:

    Hey Tom I need a simple code for naming a tab on sheet 2, from a cell A13 on sheet 1.
    is there one.

    • Tom Urtis says:

      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.

  46. Alexander says:

    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.

    • Tom Urtis says:

      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.

  47. Alexander says:

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

    • Tom Urtis says:

      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 Sub
      Change that to
      If Target.Address <> "$A$13" Then Exit Sub

      Small change #2:
      The eighth line of code above the End Sub line is
      ActiveSheet.Name = strSheetName
      Change that to
      Sheet2.Name = strSheetName

      That revised code then goes into the Sheet1 (tab name TIE IN LIST) worksheet module.

  48. Alexander says:

    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.

  49. James says:

    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!

    • Tom Urtis says:

      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

  50. Alexander says:

    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.

  51. Lee Stang says:

    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

    • Tom Urtis says:

      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.

      • Lee Stang says:

        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!

        • Tom Urtis says:

          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!

  52. Kashif Uddin Shamsi says:

    Thanks dear. It’s awesome.

  53. Eric Lee Fleming says:

    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

  54. Allan says:

    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

    • Tom Urtis says:

      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.

      • Allan says:

        I replayed to your email but I couldn’t see it now. I will wait may will appear later.

        • Tom Urtis says:

          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

          • Allan says:

            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

          • Tom Urtis says:

            You delete that code by going into the worksheet module where the code is, selecting the code and hitting the Delete key.

          • Allan says:

            I tried your code but didn’t work

          • Tom Urtis says:

            “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.

  55. Allan says:

    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

  56. Allan says:

    Thanks Tom,
    Was my mistake, I put it the stand. module.

  57. Florian says:

    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

    • Tom Urtis says:

      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.

  58. Mark says:

    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.

    • Tom Urtis says:

      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.

  59. Nikou says:

    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

  60. Nikou says:

    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

    • Tom Urtis says:

      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.

  61. Nikou says:

    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

    • Tom Urtis says:

      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.

  62. Nikou says:

    Ok thanks you ill post there. I think Ill do some cleanup based on your comments and re-post there
    thanks again

  63. Cath says:

    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

  64. Cath says:

    So sorry, answered my own silly question…

    ElseIf IsEmpty(Target) Then Exit Sub

Leave a Reply to Eric Lee Fleming Cancel reply

Your email address will not be published. Required fields are marked *

*