SOLVED

Taking worksheet names from cells in another worksheet

Copper Contributor

I have a work book with numerous worksheets and I want the names of worksheets to automatically update based on changes to certain cells in the 1st worksheet.

 

For example column A in worksheet 1 (which is called training matrix) has names of people. I want the subsequent sheets to take their names from Column A in worksheet 1 so if the name changes in that it will automatically update the name of the worksheet it relates to.

 

Is this possible?

 
7 Replies

@jkpanic 

The Sheet called "Training Matrix" in the attached contains the following Change Event code on its Module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim ws As Worksheet
Dim Cel As Range
Dim i As Long

On Error GoTo Skip
If Not Intersect(Target, Range("SheetNames")) Is Nothing Then
    Application.EnableEvents = False
    If Target <> "" Then
        i = Application.Match(Target.Value, Range("SheetNames"), 0)
        i = i + 1
        On Error Resume Next
        Set ws = Worksheets(i)
        On Error GoTo 0
        On Error GoTo Skip
        If Not ws Is Nothing Then
            Worksheets(i).Name = Target.Value
        Else
            Set ws = Worksheets.Add(after:=Worksheets(i - 1))
            ws.Name = Target.Value
        End If
    End If
End If
Application.EnableEvents = True
Exit Sub
Skip:
Application.Undo
Application.EnableEvents = True
MsgBox Err.Description, vbExclamation, Err.Number
End Sub

The sheets as per the names in A2:A11 which is a Named Range called "SheetNames" already exist in the file and if you change any name in A2:A11, the corresponding Sheet will also be renamed.

 

See if this is what you were trying to achieve.

 

 

 

 

@Subodh_Tiwari_sktneer 

Hi this is exactly what I'm looking for but do not understand how you got to that. Do not know where the "Module" is or how to put the change event code in.

 

Would appreciate some tuition.

 

Cheers

 

JK

best response confirmed by jkpanic (Copper Contributor)
Solution

@jkpanic 

To implement the code to your workbook, follow these steps...

  1. Select the Names in column A and click in the Name Box (left to Formula bar where you see the cell address) and type SheetNames so that the selected range will be treated as a Named Range called SheetNames. Refer the image at the bottom to get some visual clue.
  2. Make sure for all the names in the Named Range SheetNames, there are corresponding Sheets in the file in the same sequence as Names in column A.
  3. When you have the above setup, right click on Sheet Tab Training Matrix (the sheet which contains the Names in column A as discussed in above two steps) --> View Code and copy the code from above post and paste into the opened code window which appears after clicking on View Code.
  4. Save your workbook as Macro-Enabled Workbook.

And you are all set to test the code now.

Let me know if you have still any doubt in implementing this code to your original workbook.

Visual clue for Step1.

NamedRange.jpg

 

Visual clue for Step3.

SheetModule.jpg

 

If that takes care of your original question, please don't forget to accept the post with the proposed solution to accept as a Best Answer/Response to mark your question as Solved.

@Subodh_Tiwari_sktneer 

Cheers it works but I have one that won't change. I have attached the file and names 1 to 3 change on the tabs for the following worksheets but the 4th name (row6) doesn't change the corresponding tab but 5th name (row7) changes the tab for the 4th name??

 

Cheers

@jkpanic 

Please find the attached. I have tweaked the existing code and added a new code as I changed the approach to rename the sheets.

If you change a name in column A and if sheet for the name which you changed doesn't exist in the file, a message will be popped up informing you that a sheet doesn't exist yet for the name you changed.

 

 

@Subodh_Tiwari_sktneer 

 

Hi Subodh,

 

I am sorry to be a pain but the changes you made do not work as when I type in a name now instead of changing the name on the subsequent worksheet it just comes up with the dialog box saying worksheet name does not exist insert worksheet with that name which defeats the object of what we're trying to do. The fist code worked better except for missing that one row out of the range.

 

How do I revert back to that but fix the row to worksheet glitch?

 

Cheers

 

Julian

@jkpanic 

Thanks for confirming through PM that everything is working as desired. :thumbs_up:

1 best response

Accepted Solutions
best response confirmed by jkpanic (Copper Contributor)
Solution

@jkpanic 

To implement the code to your workbook, follow these steps...

  1. Select the Names in column A and click in the Name Box (left to Formula bar where you see the cell address) and type SheetNames so that the selected range will be treated as a Named Range called SheetNames. Refer the image at the bottom to get some visual clue.
  2. Make sure for all the names in the Named Range SheetNames, there are corresponding Sheets in the file in the same sequence as Names in column A.
  3. When you have the above setup, right click on Sheet Tab Training Matrix (the sheet which contains the Names in column A as discussed in above two steps) --> View Code and copy the code from above post and paste into the opened code window which appears after clicking on View Code.
  4. Save your workbook as Macro-Enabled Workbook.

And you are all set to test the code now.

Let me know if you have still any doubt in implementing this code to your original workbook.

Visual clue for Step1.

NamedRange.jpg

 

Visual clue for Step3.

SheetModule.jpg

 

If that takes care of your original question, please don't forget to accept the post with the proposed solution to accept as a Best Answer/Response to mark your question as Solved.

View solution in original post