Sep 20 2019 03:19 AM
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?
Sep 20 2019 04:03 AM
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.
Sep 20 2019 04:13 AM
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
Sep 20 2019 05:08 AM
SolutionTo implement the code to your workbook, follow these steps...
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.
Visual clue for Step3.
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.
Sep 20 2019 05:32 AM
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
Sep 20 2019 07:39 AM - edited Sep 20 2019 07:43 AM
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.
Sep 22 2019 10:53 PM
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
Sep 23 2019 12:55 AM
Thanks for confirming through PM that everything is working as desired. :thumbs_up:
Sep 20 2019 05:08 AM
SolutionTo implement the code to your workbook, follow these steps...
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.
Visual clue for Step3.
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.