09-20-2019 03:19 AM
09-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?
09-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.
09-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.
09-20-2019 05:08 AMSolution
To 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.
09-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??
09-20-2019 07:39 AM - edited 09-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.
09-22-2019 10:53 PM
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?
09-23-2019 12:55 AM
Thanks for confirming through PM that everything is working as desired.