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: , , , , , , , , , , , ,
196 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.

      • Ron Boezwinkle says:

        Thank you for your code to automate the renaming of the tabs when they are referencing a cell that is based on a formula! It works well….mostly.

        I’m having one problem:
        The formula I’m using to base the name of the sheets in the workbook (B) on is referencing a cell in another workbook (A). The cell I’m referencing obtains the naming information from two columns in that workbook(A); one that contains a name of a facility, and another that automatically provides a sequential number based on whether there is text in the facility name column or not. This then reports the information below to at tab in Workbook (B) which is referenced in each of the sheets in the workbook (B).

        Format would typically appear as follows:

        # Facility Name Page 4 Tab Name Page 5 Tab Name
        1 Facility A PG 4 – Facility A ProjSheet PG – 5 Facility A BldUtil
        2 Facility B PG 4 – Facility B ProjSheet PG – 5 Facility B BldUtil
        3 Facility C PG 4 – Facility C ProjSheet PG – 5 Facility C BldUtil
        4 Facility D PG 4 – Facility D ProjSheet PG – 5 Facility E BldUtil

        If I Identify that a facility needs to be added between A and B, and add it in the source sheet, an error is generated because B, C and D already exist in the existing tab names.

        I’m wondering if there is a way to first restore any tabs with “ProjSheet” and “BldUtil”in their name to their original name (Codename?) and then run the renaming macro on the individual sheets so that there isn’t the conflict? FYI: My workbook has 78 sheets, 30 of which have “ProjSheet” and 30 which have “BldgUtil” in their names.

        • Tom Urtis says:

          Wow. I am reading this a few times to follow all the factors you explained about, trying to understand how all the dots connect. This is not a criticism of you at all Ron, but it seems that this arrangement is more complicated than it needs to be. I started getting lost at “This then reports the information below to at tab in Workbook (B) which is referenced in each of the sheets in the workbook (B).”

          What caught my attention in a good way is this:
          “…and another that automatically provides a sequential number”
          To me, sequential means unique — a non-repeated number in a (supposedly) increasing sequential progression. If that is the case, the unique nature of the sheet name should be resolved. But obviously it is not resolved, so something else is intruding on that process.

          Based on your last paragraph with the questions, it sounds like you want to programmatically start from scratch name-wise and then rename the sheets based on their project CodeName (not their tab name), which I always highly recommend. A CodeName never changes but it always refers to the same worksheet object regardless of the tab name.

          Not being familiar with your workbook, generically speaking, if it were me, I would create a list of the 78 sheets’ CodeNames, which are unique, and run a macro that looks up the Facility Format combination that matches the CodeName’s sheet whose tab you want to rename. This way, you would not need to temporarily rename the tab names with some interim boilerplate syntax.

          That is my best shot at a first reply. If you get stuck on anything specific, please feel free to post back.

          • Ron Boezwinkle says:

            Thanks for the reply! You are absolutely correct and I was overcomplicating the issues.

            I’ve combined the two workbooks to minimize the complexity. I have the following formula in the cell I’m referencing to obtain the sheet name:

            =”PG 4 – “&XLOOKUP(A3,’Property Listing’!B11:B54,’Property Listing’!A11:A54)&” “&XLOOKUP(A3,’Property Listing’!B11:B54,’Property Listing’!C11:C54)&” ProjSht”

            That looks for the property name selected from a drop-down list in cell A:1. It looks for that name in column B in the property listing sheet and returns both a unique property number, and an abbreviated property code which looks like this and is located in cell C3:

            PG 4 – 6 DDE ProjSht

            That part works perfectly and changes automatically based on the selection from the drop-down.

            The code below has been pasted into the individual sheets I want to automatically name based on the contents of cell C3:

            ‘The following code changes the name of the worksheet to the input found in cell C3
            ‘This code came from https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-name-your-sheet-tab-same-as-cell-value/
            Private Sub Worksheet_Calculate()
            ‘Specify the target cell whose entry shall be the sheet tab name.
            With Range(“C3”)
            If Len(.Value) = 0 Or Len(.Value) > 31 Then Exit Sub

            ‘Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
            ‘Verify that none of these characters are present in the cell’s entry.
            Dim IllegalCharacter(1 To 7) As String, i As Integer
            IllegalCharacter(1) = “/”
            IllegalCharacter(2) = “\”
            IllegalCharacter(3) = “[”
            IllegalCharacter(4) = “]”
            IllegalCharacter(5) = “*”
            IllegalCharacter(6) = “?”
            IllegalCharacter(7) = “:”
            For i = 1 To 7
            If InStr(.Text, (IllegalCharacter(i))) > 0 Then
            MsgBox “The formula in cell C3 returns a value containing a character that violates sheet naming rules.” & vbCrLf & _
            “Recalculate the formula without the ”” & IllegalCharacter(i) & “” character.”, _
            48, “Not a possible sheet name !!”
            Exit Sub
            End If
            Next i

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

            If bln = False Then
            ActiveSheet.Name = strSheetName
            ElseIf ActiveSheet.Name .Text Then
            MsgBox “There is already a sheet named ” & strSheetName & “.” & vbCrLf & _
            “Recalculate the formula in cell C3 to return a unique name.”
            End If

            End With
            End Sub

            The code is trying to update the tab names, but two things are happening:

            1.) The sheet name for my ‘Property Listing’ tab is changing to the value of the first tab that needs to be renamed.
            2.) I’m receiving the following error box(es): “There is already a sheet named xxxx. Recalculate the formula in cell C3 to return a unique name.”

            If I rename the “Property Listing” tab and then go to C3, if I hit F2 + enter, it updates the tab name correctly.

            Any ideas as to how to prevent this? I’ve checked and there is no VBA code in the ‘Property Listing’ sheet.

  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

      • Allister says:

        HI

        I am trying to use your macro

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

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

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

        • Tom Urtis says:

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

  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

          • 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

  65. Heather says:

    Hi,
    I am hoping you will be able to help me 🙂 I’m brand new to VBA and have taught myself the little pieces I do know. I recently built the following Macro, that duplicates a “master” tab when a specific cell is populated with specific text:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(“A5:A200”)) Is Nothing Then
    Select Case Range(“A5”)
    Case “ACUM”: CopyRename
    Case “AFTR”: CopyRename
    Case “ALG”: CopyRename
    Case “ALGT”: CopyRename
    Case “ALMT”: CopyRename
    Case “AMB”: CopyRename
    Case “AUTB”: CopyRename
    Case “BASE”: CopyRename
    Case “BLD”: CopyRename
    Case “CDIP”: CopyRename
    Case “CDOP”: CopyRename
    Case “CDO1”: CopyRename
    Case “CDRR”: CopyRename
    Case “CHIR”: CopyRename
    Case “CMPL”: CopyRename
    Case “COIN”: CopyRename
    Case “CRCS”: CopyRename
    Case “DED”: CopyRename
    Case “DED1”: CopyRename
    Case “DED2”: CopyRename
    Case “DED3”: CopyRename
    Case “DED4”: CopyRename
    Case “DED5”: CopyRename
    Case “DEN”: CopyRename
    Case “DMEB”: CopyRename
    Case “DMES”: CopyRename
    Case “DPP”: CopyRename
    Case “DUMY”: CopyRename
    Case “EMER”: CopyRename
    Case “EMPH”: CopyRename
    Case “EPOT”: CopyRename
    Case “EXAB”: CopyRename
    Case “FAM”: CopyRename
    Case “GRP”: CopyRename
    Case “GYN”: CopyRename
    Case “HC”: CopyRename
    Case “HCSS”: CopyRename
    Case “HEAR”: CopyRename
    Case “HH”: CopyRename
    Case “HLTH”: CopyRename
    Case “HOOP”: CopyRename
    Case “HOS2”: CopyRename
    Case “HOSP”: CopyRename
    Case “HRA”: CopyRename
    Case “HRA2”: CopyRename
    Case “INFT”: CopyRename
    Case “LAB”: CopyRename
    Case “LTM”: CopyRename
    Case “MHBI”: CopyRename
    Case “MHBO”: CopyRename
    Case “MHIP”: CopyRename
    Case “MHOP”: CopyRename
    Case “OPM”: CopyRename
    Case “OPM1”: CopyRename
    Case “OPM2”: CopyRename
    Case “OPM3”: CopyRename
    Case “OPM4”: CopyRename
    Case “OPM5”: CopyRename
    Case “OPM6”: CopyRename
    Case “OPT”: CopyRename
    Case “OVC”: CopyRename
    Case “OVC2”: CopyRename
    Case “OVC3”: CopyRename
    Case “OVC4”: CopyRename
    Case “OVC5”: CopyRename
    Case “OVC6”: CopyRename
    Case “OVC7”: CopyRename
    Case “OVC8”: CopyRename
    Case “OXYG”: CopyRename
    Case “PP”: CopyRename
    Case “PNMT”: CopyRename
    Case “ORTH”: CopyRename
    Case “PV”: CopyRename
    Case “REHB”: CopyRename
    Case “REOP”: CopyRename
    Case “RX01”: CopyRename
    Case “RX02”: CopyRename
    Case “RX03”: CopyRename
    Case “RX04”: CopyRename
    Case “RX05”: CopyRename
    Case “RX06”: CopyRename
    Case “SGOP”: CopyRename
    Case “SNF”: CopyRename
    Case “SNKR”: CopyRename
    Case “SPVC”: CopyRename
    Case “STU”: CopyRename
    Case “TABS”: CopyRename
    Case “TRAN”: CopyRename
    Case “TRGN”: CopyRename
    Case “TRVL”: CopyRename
    Case “WLCH”: CopyRename
    Case “XPRO”: CopyRename
    Case “XRAY”: CopyRename
    Case “VADD”: CopyRename
    Case “DIAL”: CopyRename
    Case “EFED”: CopyRename
    Case “EP2R”: CopyRename
    Case “EPO”: CopyRename
    Case “FRST”: CopyRename
    Case “GYN1”: CopyRename
    Case “HEA1”: CopyRename
    Case “KPCV”: CopyRename
    Case “MDCV”: CopyRename
    Case “MDEX”: CopyRename
    Case “MHO1”: CopyRename
    Case “REO1”: CopyRename
    Case “REXC”: CopyRename
    Case “RHLF”: CopyRename
    Case “RHLP”: CopyRename
    Case “RHPQ”: CopyRename
    Case “RHSC”: CopyRename
    Case “RKPO”: CopyRename
    Case “RTHA”: CopyRename
    Case “RX”: CopyRename
    Case “SPV1”: CopyRename
    End Select
    End If
    If Not Intersect(Target, Range(“J5:J200”)) Is Nothing Then
    Select Case Range(“J5”)
    Case “ACUM”: CopyRename
    Case “AFTR”: CopyRename
    Case “ALG”: CopyRename
    Case “ALGT”: CopyRename
    Case “ALMT”: CopyRename
    Case “AMB”: CopyRename
    Case “AUTB”: CopyRename
    Case “BASE”: CopyRename
    Case “BLD”: CopyRename
    Case “CDIP”: CopyRename
    Case “CDOP”: CopyRename
    Case “CDO1”: CopyRename
    Case “CDRR”: CopyRename
    Case “CHIR”: CopyRename
    Case “CMPL”: CopyRename
    Case “COIN”: CopyRename
    Case “CRCS”: CopyRename
    Case “DED”: CopyRename
    Case “DED1”: CopyRename
    Case “DED2”: CopyRename
    Case “DED3”: CopyRename
    Case “DED4”: CopyRename
    Case “DED5”: CopyRename
    Case “DEN”: CopyRename
    Case “DMEB”: CopyRename
    Case “DMES”: CopyRename
    Case “DPP”: CopyRename
    Case “DUMY”: CopyRename
    Case “EMER”: CopyRename
    Case “EMPH”: CopyRename
    Case “EPOT”: CopyRename
    Case “EXAB”: CopyRename
    Case “FAM”: CopyRename
    Case “GRP”: CopyRename
    Case “GYN”: CopyRename
    Case “HC”: CopyRename
    Case “HCSS”: CopyRename
    Case “HEAR”: CopyRename
    Case “HH”: CopyRename
    Case “HLTH”: CopyRename
    Case “HOOP”: CopyRename
    Case “HOS2”: CopyRename
    Case “HOSP”: CopyRename
    Case “HRA”: CopyRename
    Case “HRA2”: CopyRename
    Case “INFT”: CopyRename
    Case “LAB”: CopyRename
    Case “LTM”: CopyRename
    Case “MHBI”: CopyRename
    Case “MHBO”: CopyRename
    Case “MHIP”: CopyRename
    Case “MHOP”: CopyRename
    Case “OPM”: CopyRename
    Case “OPM1”: CopyRename
    Case “OPM2”: CopyRename
    Case “OPM3”: CopyRename
    Case “OPM4”: CopyRename
    Case “OPM5”: CopyRename
    Case “OPM6”: CopyRename
    Case “OPT”: CopyRename
    Case “OVC”: CopyRename
    Case “OVC2”: CopyRename
    Case “OVC3”: CopyRename
    Case “OVC4”: CopyRename
    Case “OVC5”: CopyRename
    Case “OVC6”: CopyRename
    Case “OVC7”: CopyRename
    Case “OVC8”: CopyRename
    Case “OXYG”: CopyRename
    Case “PP”: CopyRename
    Case “PNMT”: CopyRename
    Case “ORTH”: CopyRename
    Case “PV”: CopyRename
    Case “REHB”: CopyRename
    Case “REOP”: CopyRename
    Case “RX01”: CopyRename
    Case “RX02”: CopyRename
    Case “RX03”: CopyRename
    Case “RX04”: CopyRename
    Case “RX05”: CopyRename
    Case “RX06”: CopyRename
    Case “SGOP”: CopyRename
    Case “SNF”: CopyRename
    Case “SNKR”: CopyRename
    Case “SPVC”: CopyRename
    Case “STU”: CopyRename
    Case “TABS”: CopyRename
    Case “TRAN”: CopyRename
    Case “TRGN”: CopyRename
    Case “TRVL”: CopyRename
    Case “WLCH”: CopyRename
    Case “XPRO”: CopyRename
    Case “XRAY”: CopyRename
    Case “VADD”: CopyRename
    Case “DIAL”: CopyRename
    Case “EFED”: CopyRename
    Case “EP2R”: CopyRename
    Case “EPO”: CopyRename
    Case “FRST”: CopyRename
    Case “GYN1”: CopyRename
    Case “HEA1”: CopyRename
    Case “KPCV”: CopyRename
    Case “MDCV”: CopyRename
    Case “MDEX”: CopyRename
    Case “MHO1”: CopyRename
    Case “REO1”: CopyRename
    Case “REXC”: CopyRename
    Case “RHLF”: CopyRename
    Case “RHLP”: CopyRename
    Case “RHPQ”: CopyRename
    Case “RHSC”: CopyRename
    Case “RKPO”: CopyRename
    Case “RTHA”: CopyRename
    Case “RX”: CopyRename
    Case “SPV1”: CopyRename
    End Select
    End If
    End Sub
    it works phenomenally! However, I am now trying to build a macro (similar to yours above I believe) that will auto-name the new tabs with the values that are concatenated with the current macro. (ie: Cell A5:A200 is what triggers the current macro, there are also values in B5:B200 and the values in A5:A200 are concatenated together in C5:C200) I want the new macro to use the values that are loaded in C5:C200 to be the names of the new tabs created when a value is populated in A5:A200.
    Is there any way to do this?
    Thank You,
    Heather

    • Heather says:

      An Example of the Concatenate Cells is: ALMT – B5
      I have it set up to include a space in-between each value and includes a -. ALMT is what is in cell A5 and B5 is what is in cell C5.

      I also forgot to mention that all of cells A5:A200 are values that are compiled via a drop down list to trigger the current macro.

      Thank You So Much in advance for your help!

      • Tom Urtis says:

        A lot of cases there. A few questions:

        (1)
        Why monitor changes to all cells in A5:A200 if you only care about cells A5 and J5 per your two Select Case lines.

        (2)
        What exactly is “CopyRename”?

        (3)
        This drop down list that you mentioned, first, I assume it is data validation, but can you confirm that.

        (4)
        More importantly, are those items such as ACUM, AFTR and so on the ***ONLY*** values allowed in the cells.

        (5)
        Are there worksheets whose tab names are those drop down items? I’m not clear on what you are doing and what all these cases are for and why only 2 cells (A5 and J5) are monitored for changes.

        • Heather says:

          Hi Tom, Thank you for the quick response!

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

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

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

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

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

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

          • Tom Urtis says:

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

    • Tom Urtis says:

      See my reply to your follow-up comment.

  66. Jason says:

    Hi Tom

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

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

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

    Thanks

    • Tom Urtis says:

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

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

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

      • Jason says:

        Thanks for your quick reply in answer to your questions:

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

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

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

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

        3 it doesn’t have any of the check functions your code has.

        Sub RenameSheet()

        Sheet10.Name = Sheet10.Range(“A1”)

        Sheet12.Name = Sheet12.Range(“A1”)
        Sheet13.Name = Sheet13.Range(“A1”)
        Sheet14.Name = Sheet14.Range(“A1”)
        Sheet15.Name = Sheet15.Range(“A1”)
        Sheet16.Name = Sheet16.Range(“A1”)
        Sheet17.Name = Sheet17.Range(“A1”)
        Sheet18.Name = Sheet18.Range(“A1”)
        Sheet19.Name = Sheet19.Range(“A1”)
        Sheet20.Name = Sheet20.Range(“A1”)
        Sheet21.Name = Sheet21.Range(“A1”)
        Sheet22.Name = Sheet22.Range(“A1”)
        Sheet23.Name = Sheet23.Range(“A1”)
        Sheet24.Name = Sheet24.Range(“A1”)
        Sheet25.Name = Sheet25.Range(“A1”)
        Sheet26.Name = Sheet26.Range(“A1”)
        Sheet27.Name = Sheet27.Range(“A1”)
        Sheet28.Name = Sheet28.Range(“A1”)
        Sheet29.Name = Sheet29.Range(“A1”)
        Sheet30.Name = Sheet30.Range(“A1”)

        End Sub

        • Tom Urtis says:

          There is a loop recommendation I can make, but before that, is there a reason why you skip Sheet11 although there seems to be a space for it in your code. What is so special about Sheet11.

          • Jason says:

            Sheet 11 was a master sheet that I created but its not a problem if the sheets need to be sequential I can move the data around if necessary.

            Thanks

            • Tom Urtis says:

              Keep it simple and use Sheet11 as you have Sheet10 and Sheets 12 to 30.

              This should do what you want.

              Sub ChangeSheetNames()
              Dim xCounter As Integer, strNewName As String

              For xCounter = 10 To 30
              With Sheets("Sheet" & xCounter)
              strNewName = .Range("A1").Value

              'Disallow if it is greater than 31 characters.
              If Len(strNewName) > 31 Then
              MsgBox "Worksheet names cannot be more than 31 characters." & vbCrLf & _
              strNewName & " has " & Len(strNewName) & " characters.", _
              48, "Keep it under 31 characters."
              Exit Sub
              End If

              'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
              'Verify that none of these characters are present in the cell's entry.
              Dim IllegalCharacter(1 To 7) As String, i As Integer
              IllegalCharacter(1) = "/"
              IllegalCharacter(2) = "\"
              IllegalCharacter(3) = "["
              IllegalCharacter(4) = "]"
              IllegalCharacter(5) = "*"
              IllegalCharacter(6) = "?"
              IllegalCharacter(7) = ":"
              For i = 1 To 7
              If InStr(strNewName, (IllegalCharacter(i))) > 0 Then
              MsgBox "You used a character that violates sheet naming rules." & vbCrLf & _
              "Enter a name without the ''" & IllegalCharacter(i) & "'' character.", _
              48, "Not a possible sheet name !!"
              Exit Sub
              End If
              Next i

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

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

              End With
              Next xCounter

              End Sub

  67. Jason says:

    Hi I must be missing something I’ve entered the below on the cover sheet I am using to populate the names of the other sheets. Do you think it’s an issue with “strNewName = .Range(“G15:G35″).Value”

    Sub ChangeSheetNames()
    Dim xCounter As Integer, strNewName As String

    For xCounter = 10 To 30
    With Sheets(“Sheet” & xCounter)
    strNewName = .Range(“G15:G35”).Value

    ‘Disallow if it is greater than 31 characters.
    If Len(strNewName) > 31 Then
    MsgBox “Worksheet names cannot be more than 31 characters.” & vbCrLf & _
    strNewName & ” has ” & Len(strNewName) & ” characters.”, _
    48, “Keep it under 31 characters.”
    Exit Sub
    End If

    ‘Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
    ‘Verify that none of these characters are present in the cell’s entry.
    Dim IllegalCharacter(1 To 7) As String, i As Integer
    IllegalCharacter(1) = “/”
    IllegalCharacter(2) = “\”
    IllegalCharacter(3) = “[”
    IllegalCharacter(4) = “]”
    IllegalCharacter(5) = “*”
    IllegalCharacter(6) = “?”
    IllegalCharacter(7) = “:”
    For i = 1 To 7
    If InStr(strNewName, (IllegalCharacter(i))) > 0 Then
    MsgBox “You used a character that violates sheet naming rules.” & vbCrLf & _
    “Enter a name without the ”” & IllegalCharacter(i) & “” character.”, _
    48, “Not a possible sheet name !!”
    Exit Sub
    End If
    Next i

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

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

    End With
    Next xCounter

    End Sub

    Thanks again

    Jason

    • Tom Urtis says:

      Change this
      strNewName = .Range(“G15:G35”).Value

      to this
      strNewName = .Cells(xCounter + 5, 7).Value

      • Jason says:

        Thanks Tom I get a run-time error 9 Subscript out of range. I think there’s also a problem with this line

        With Sheets(“Sheet” & xCounter) I think “sheet” is referencing a sheet named “sheet” rather than the VB sheet number. I have tried removing the “” to no avail any thoughts?

        Ta

        • Tom Urtis says:

          I assumed that you were relying in the worksheets’ default names (and not index numbers) starting with the sheets you care about as the default Sheet10, Sheet11, and so on up to Sheet30.

          Guessing without seeing your workbook, but maybe all that needs to be done now is to change this
          With Sheets(“Sheet” & xCounter)
          to this
          With Sheets(xCounter)

          which will deal with those worksheets in their indexed position of tenth through and including thirtieth.

          • Jason says:

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

            Sub ChangeSheetNames()
            Dim xCounter As Integer, strNewName As String

            For xCounter = 10 To 30
            With Sheets(xCounter)
            strNewName = .Range(“A1”).Value

            ‘Disallow if it is greater than 31 characters.
            If Len(strNewName) > 31 Then
            MsgBox “Worksheet names cannot be more than 31 characters.” & vbCrLf & _
            strNewName & ” has ” & Len(strNewName) & ” characters.”, _
            48, “Keep it under 31 characters.”
            Exit Sub
            End If

            ‘Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
            ‘Verify that none of these characters are present in the cell’s entry.
            Dim IllegalCharacter(1 To 7) As String, i As Integer
            IllegalCharacter(1) = “/”
            IllegalCharacter(2) = “\”
            IllegalCharacter(3) = “[”
            IllegalCharacter(4) = “]”
            IllegalCharacter(5) = “*”
            IllegalCharacter(6) = “?”
            IllegalCharacter(7) = “:”
            For i = 1 To 7
            If InStr(strNewName, (IllegalCharacter(i))) > 0 Then
            MsgBox “You used a character that violates sheet naming rules.” & vbCrLf & _
            “Enter a name without the ”” & IllegalCharacter(i) & “” character.”, _
            48, “Not a possible sheet name !!”
            Exit Sub
            End If
            Next i

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

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

            End With
            Next xCounter

            End Sub

          • Jason says:

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

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

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

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

            Would this be easier to achieve?

            • Tom Urtis says:

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

  68. Esther says:

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

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

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

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

    I’d appreciate any insights you are willing to offer!

    • Tom Urtis says:

      Hello, Esther – –

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

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

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

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

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

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

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

      • Esther says:

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

        • Esther says:

          Oh, forgot to mention, I copied your original code above and only changed the on $A$1 reference to $C$2. Everything else is the same.

        • Tom Urtis says:

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

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

          • Esther says:

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

            The first sub-routine is:

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

            • Tom Urtis says:

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

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

  69. Esther says:

    Tom, when I View Source from Sheet1, I do not see the Worksheet_Change code. I put the code on the This Workbook item in the VBA Console. Sheet1 is blank, as are the other sheets.

    • Tom Urtis says:

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

      • Esther says:

        So, I need to copy it to all 26 worksheets? I want all the worksheets to update when I change the date on the first worksheet.

        Sorry, it does say View Code. My mistake.

        • Tom Urtis says:

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

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

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

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

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

          • Esther says:

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

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

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

            • Tom Urtis says:

              Format your C2 cell as Text. Then enter in a date with characters that do not violate sheet naming rules. All those characters, including the forward slash, are included in my code.

  70. Esther says:

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

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

  71. Ahrem says:

    I encounter Run time error 424 Object required with the first code.

    If Target.Address “$b$4” Then

  72. Mitchell says:

    I’m trying to use this code to change the tabs on sheets 6-20 automatically to match cells on their respective sheets. However these cells on sheets 6-20 are themselves referencing cells on sheet 1 (ie =’sheet1′!e13). This code doesn’t appear to recognize changes to the cells on sheet 1 because they’re looking for changes in the cell in their respective sheets, which will always remain =’sheet1′!E13.

    Is it possible to reference sheet 1 for the tabs names of sheets 6-20?

    • Tom Urtis says:

      The short answer is yes.

      The longer answer is, you can use the Workbook level Calculate event (so you don’t need to install 15 separate event codes into sheets 6-20’s worksheet modules) for sheets 6-20 to monitor whichever cell address that holds the changing sheet name. You did not say what the cell address is, or what they are if each sheet 6-20 has a different cell referring to Sheet1. Or, you can monitor the range in Sheet1 where (presumably) you have listed 15 cells that each contain names corresponding to sheets 6-20, with a Change event that changes the sheet tab names for those respective cells. The only issue with that is that the worksheet object codename would be needed to associate which cell in Sheet1 (you also did not say what range location is in Sheet1 except for cell E13) corresponds to which sheet tab.

      What you want to do is do-able, it just depends on which method or tactic you want to use to go about it.

      • Mitchell says:

        Thanks Tom! Sorry for the vague nature of my question. I just started learning all of this, so I’m not very well versed in the language and necessary details to convey the full scope of my inquiry. I’ve changed the references a little bit below to match the changes i’ve made to my workbook. Hopefully this is more clear.

        What I want is for whatever is typed into cells B12:B41 in sheet1 to change the names of worksheet 6-35 respectively. So each worksheet would have a different name corresponding to one of the cells in that range. I think this matches your second method. Can I just use your above code and change the target cell to one on sheet 1? What’s the syntax change involved? Or is there a better way to do that?

        • Tom Urtis says:

          No worries – – everyone was a beginner once.

          OK, so it is not 15 sheets, it is 30 sheets. That in and of itself is no problem.

          To me, the second method, which is the direction you are thinking of, would be the better of the two, IF (notice the caps for emphasis) you can do a once-only bit of housekeeping, UNLESS there will NEVER be a case where the order (in VBA speak, the Index number) of the worksheets changes. So you need to tell me if it is IMPOSSIBLE that the sheet tabs’ order from left to right will NEVER change. The chances of that being the case are the same as a vaccine ready to cure Covid by midnight tonight. Probably won’t happen. But I am asking anyway, so is it yes (not likely) that the order of the sheet tabs will always and forever remain constant, or no, that a possibility exists that the worksheets as they are sequenced now will change their order sooner or later.

          • Mitchell says:

            The intent is that they will remain the same always. Only thing that should change is the content of the reference cell

            • Tom Urtis says:

              This will do what you want.
              Copy it to your clipboard.
              Right-click onto your Sheet1 tab.
              Left click onto View Code.
              Paste the code into the large white area that is Sheet1’s worksheet module.
              Press Alt+Q to close the Visual Basic Editor.
              Works when I tested it.


              Private Sub Worksheet_Change(ByVal Target As Range)
              If Intersect(Target, Range("B12:B41")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
              If IsEmpty(Target) Then
              Application.EnableEvents = False
              Application.Undo
              Application.EnableEvents = True
              MsgBox "You cannot clear the contents or a cell." & vbCrLf & "You can only edit the contents.", 16, "Can't do that."
              Exit Sub
              End If
              If Len(Target.Value) > 31 Then
              MsgBox "Worksheet names cannot be more than 31 characters." & vbCrLf & _
              Target.Value & " has " & Len(Target.Value) & " characters.", _
              48, "Keep it under 31 characters."
              Application.EnableEvents = False
              Target.Value = "XXXXX"
              Application.EnableEvents = True
              Exit Sub
              End If
              Dim IllegalCharacter(1 To 7) As String, i As Integer
              IllegalCharacter(1) = "/"
              IllegalCharacter(2) = "\"
              IllegalCharacter(3) = "["
              IllegalCharacter(4) = "]"
              IllegalCharacter(5) = "*"
              IllegalCharacter(6) = "?"
              IllegalCharacter(7) = ":"
              For i = 1 To 7
              If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
              MsgBox "You used a character that violates sheet naming rules." & vbCrLf & _
              "Enter a name without the " & IllegalCharacter(i) & " character.", _
              48, "Not a possible sheet name !"
              Application.EnableEvents = False
              Target.Value = "XXXXXX"
              Application.EnableEvents = True
              Exit Sub
              End If
              Next i
              Dim strSheetName As String, wks As Worksheet, bln As Boolean
              strSheetName = Trim(Target.Value)
              On Error Resume Next
              Set wks = ActiveWorkbook.Worksheets(strSheetName)
              On Error Resume Next
              If Not wks Is Nothing Then
              bln = True
              Else
              bln = False
              Err.Clear
              End If
              If bln = False Then
              Dim xRow&
              xRow = Target.Row
              Sheets(xRow - 6).Name = Target.Value
              Else
              MsgBox "You already named a sheet " & strSheetName & "." & vbCrLf & _
              "Enter a unique name for this sheet."
              Application.EnableEvents = False
              Target.Value = "XXXXXXX"
              Application.EnableEvents = True
              End If
              End Sub

  73. Mitchell says:

    That was perfect. Appreciate the help!

  74. Neil says:

    Hi Tom,
    Hope you are well.
    I have used the code in the post from October 23, 2013 for changes to multiple sheets names from a single sheet. This works well. However is there a way to make it so the reference cells on sheet 1 can move.
    they are currently A22 to A27 inclusive and i would like to be able to move some of the cells to M22 to M27 incluive for example.
    Please see my code below for reference:

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

    ‘Disallow the entry if it is greater than 31 characters.
    If Len(Target.Value) > 31 Then
    MsgBox “Worksheet names cannot be more than 31 characters.” & vbCrLf & _
    Target.Value & ” has ” & Len(Target.Value) & ” characters.”, _
    48, “Keep it under 31 characters.”
    Application.EnableEvents = False
    Target.ClearContents
    Application.EnableEvents = True
    Exit Sub
    End If

    ‘Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
    ‘Verify that none of these characters are present in the cell’s entry.
    Dim IllegalCharacter(1 To 7) As String, i As Integer
    IllegalCharacter(1) = “ / ”
    IllegalCharacter(2) = “ \ ”
    IllegalCharacter(3) = “[”
    IllegalCharacter(4) = “]”
    IllegalCharacter(5) = “ * ”
    IllegalCharacter(6) = “?”
    IllegalCharacter(7) = “: ”
    For i = 1 To 7
    If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
    MsgBox “You used a character that violates sheet naming rules.” & vbCrLf & _
    “Enter a name without the ”” & IllegalCharacter(i) & “” character.”, _
    48, “Not a possible sheet name !!”
    Application.EnableEvents = False
    Target.ClearContents
    Application.EnableEvents = True
    Exit Sub
    End If
    Next i

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

    ‘If the worksheet name does not already exist, name the sheet as cell value.
    ‘Otherwise, advise the user that duplicate sheet names are not allowed.
    If bln = False Then
    Select Case Target.Address
    Case “$A$22”: Sheet2.Name = strSheetName
    Case “$A$23”: Sheet3.Name = strSheetName
    Case “$A$24”: Sheet4.Name = strSheetName
    Case “$A$25”: Sheet5.Name = strSheetName
    Case “$A$26”: Sheet6.Name = strSheetName
    Case “$A$27”: Sheet7.Name = strSheetName
    End Select
    Else
    MsgBox “There is already a sheet named ” & strSheetName & “.” & vbCrLf & _
    “Please enter a unique name for this sheet.”
    Application.EnableEvents = False
    Target.ClearContents
    Application.EnableEvents = True
    End If

    End Sub

    Many thanks in advance
    Very best regards
    Neil

    • Tom Urtis says:

      Hello, Neil – –

      Taking your question literally, yes you can move your target cells on Sheet1 from A22:A27 to M22:M27 with these quick changes to the code:

      Change this line:
      If Intersect(Target, Range("A22,A23,A24,A25,A26,A27")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
      to this:
      If Intersect(Target, Range("M22:M27")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

      and change your Case addresses from this:

      Select Case Target.Address
      Case "$A$22": Sheet2.Name = strSheetName
      Case "$A$23": Sheet3.Name = strSheetName
      Case "$A$24": Sheet4.Name = strSheetName
      Case "$A$25": Sheet5.Name = strSheetName
      Case "$A$26": Sheet6.Name = strSheetName
      Case "$A$27": Sheet7.Name = strSheetName
      End Select

      to this:

      Select Case Target.Address
      Case "$M$22": Sheet2.Name = strSheetName
      Case "$M$23": Sheet3.Name = strSheetName
      Case "$M$24": Sheet4.Name = strSheetName
      Case "$M$25": Sheet5.Name = strSheetName
      Case "$M$26": Sheet6.Name = strSheetName
      Case "$M$27": Sheet7.Name = strSheetName
      End Select

      Assuming your associated sheet object codenames are the same, this should do what you want.

      • Neil says:

        Hi Tom,
        Many thanks for your help.
        It is greatly appreciated.

        Looking at the way I want this to work is the contents of A22 will remain in the same place, but i may want to move the contents of the A23 to M22, this way i can do a comparison of costs between A22 and M22.

        I have actually used code so i can paste the contents of the cell from A23 in to M22, A24 in to M23 and so on using the following.

        Case “$A$22”: Sheet2.Name = strSheetName
        Case “$A$23”: Sheet3.Name = strSheetName
        Case “$A$24”: Sheet4.Name = strSheetName
        Case “$A$25”: Sheet5.Name = strSheetName
        Case “$A$26”: Sheet6.Name = strSheetName
        Case “$A$27”: Sheet7.Name = strSheetName
        Case “$M$22”: Sheet3.Name = strSheetName
        Case “$M$23”: Sheet4.Name = strSheetName
        Case “$M$24”: Sheet5.Name = strSheetName
        Case “$M$25”: Sheet6.Name = strSheetName
        Case “$M$26”: Sheet7.Name = strSheetName

        Thanks again for your help.
        Very best regards

  75. Juan Aragon says:

    Hello Tom,

    How can I use your formula and display a prompt vice existing the macro when characters are 0 or greater than 31?

    If greater than 31, can you make it that it only renames with the first 25 characters of the cell?

    Also, to avoid duplicate name errors, how can you the sheet be renamed using two cells, vice just one.

    Here is what I have, and is not working.

    Private Sub Worksheet_Calculate()
    With Range(“B3”)
    If Len(.Value) = 0 Then
    MsgBox “Worksheet names cannot be 0 characters.” & vbCrLf & _
    Target.Value & ” has ” & Len(Target.Value) & ” characters.”, _
    48, “Rename manually.”
    Application.EnableEvents = False
    Target.ClearContents
    Application.EnableEvents = True
    Exit Sub
    End If
    ‘Disallow the entry if it is greater than 31 characters.
    If Len(Target.Value) > 31 Then
    MsgBox “Worksheet names cannot be more than 31 characters.” & vbCrLf & _
    Target.Value & ” has ” & Len(Target.Value) & ” characters.”, _
    48, “Keep it under 31 characters.”
    Application.EnableEvents = False
    Target.ClearContents
    Application.EnableEvents = True
    Exit Sub
    End If

  76. Kirk Brinkerhoff says:

    Thanks, Tom for getting me here to this page. My issue is that I want to name the pages on the bottom of my spreadsheet to reflect the names of the students that are in my class. I have a list of up to 30 students and each student is given their very own excel page where I keep notes and observations of that student throughout the entire training period. I do not know how to rename the page tabs at the bottom of the spreadsheet to reflect the names of each student. I sent you some screenshots of the worksheet. part of my problem is that this spreadsheet is kind of a house that jack built and too many people have added to it over the years and they won’t let me make a new one, so ‘i am trying to doctor it up. Can you please help. Thank you so much!

    • Tom Urtis says:

      In a previous email I asked you for the range of cells on the landing page where each of the 3 columns are. I am still waiting for you to tell me that.
      What range is the list of 30 Specialist numbers.
      What range is the list of 30 peoples names.
      What range is the list of 30 sheet codenames that correspond to the list of 30 Specialist names.

  77. Niels Knudsen says:

    Hi Tom,

    What do i need to change in the code, if I want to place it as a sub in one of the modules?
    I’ve another sub that is called/executed before this one, that changes the values of the target cells – ie “A1” in the first example.

    I’ve got the first sub running and working, but when it calls this code it errors with “Runtime error 424” and in debugging it highlights this line:

    “If Intersect(Target, Range(“Timer!C2:I2,Timer!C21:I21″)) Is Nothing Or Target.Cells.Count > 1 Then”

    Thank you for time and effort.

    Copy of the complete code:

    Sub arknavneskift()

    ‘Ændrer arknavnene til de valgte datoer

    ‘Specify the target cell whose entry shall be the sheet tab name.
    If Intersect(Target, Range(“Timer!C2:I2,Timer!C21:I21”)) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    ‘If the target cell is empty (contents cleared) do not change the sheet name.
    If IsEmpty(Target) Then Exit Sub

    ‘Disallow the entry if it is greater than 31 characters.
    If Len(Target.Value) > 31 Then
    MsgBox “Arknavne kan ikke være længere end 31 tegn.” & vbCrLf & _
    Target.Value & ” er ” & Len(Target.Value) & ” tegn.”, _
    48, “Hold det under 32 tegn, kontroller datoerne i kalender arket.”
    Application.EnableEvents = False
    Target.ClearContents
    Application.EnableEvents = True
    Exit Sub
    End If

    ‘Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
    ‘Verify that none of these characters are present in the cell’s entry.
    Dim IllegalCharacter(1 To 7) As String, i As Integer
    IllegalCharacter(1) = “/”
    IllegalCharacter(2) = “\”
    IllegalCharacter(3) = “[”
    IllegalCharacter(4) = “]”
    IllegalCharacter(5) = “*”
    IllegalCharacter(6) = “?”
    IllegalCharacter(7) = “:”
    For i = 1 To 7
    If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
    MsgBox “Du har brugt et tegn, som excel ikke vil acceptere i arknavne.” & vbCrLf & _
    “Vælg et navn uden ”” & IllegalCharacter(i) & “” tegnet.”, _
    48, “Dette er ikke et gyldigt ark navn!”
    Application.EnableEvents = False
    Target.ClearContents
    Application.EnableEvents = True
    Exit Sub
    End If
    Next i

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

    ‘If the worksheet name does not already exist, name the sheet as cell value.
    ‘Otherwise, advise the user that duplicate sheet names are not allowed.
    If bln = False Then
    Select Case Target.Address
    Case “Timer!$C$2”: Ark4.Name = strSheetName
    Case “Timer!$D$2”: Ark5.Name = strSheetName
    Case “Timer!$E$2”: Ark6.Name = strSheetName
    Case “Timer!$F$2”: Ark7.Name = strSheetName
    Case “Timer!$G$2”: Ark8.Name = strSheetName
    Case “Timer!$H$2”: Ark9.Name = strSheetName
    Case “Timer!$I$2”: Ark10.Name = strSheetName
    Case “Timer!$C$21”: Ark11.Name = strSheetName
    Case “Timer!$D$21”: Ark12.Name = strSheetName
    Case “Timer!$E$21”: Ark13.Name = strSheetName
    Case “Timer!$F$21”: Ark14.Name = strSheetName
    Case “Timer!$G$21”: Ark15.Name = strSheetName
    Case “Timer!$H$21”: Ark16.Name = strSheetName
    Case “Timer!$I$21”: Ark17.Name = strSheetName
    End Select
    Else
    MsgBox “Der er allerede et ark med navnet ” & strSheetName & “.” & vbCrLf & _
    “Vælg et unikt navn til dette ark.”
    Application.EnableEvents = False
    Target.ClearContents
    Application.EnableEvents = True
    End If

    ‘https://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-name-your-sheet-tab-same-as-cell-value/

    End Sub

Leave a Reply to Des K Cancel reply

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

*