Forum Discussion
Taking worksheet names from cells in another worksheet
To implement the code to your workbook, follow these steps...
- 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.
- 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.
- 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.
- 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.
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.
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 SubThe 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.
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
To implement the code to your workbook, follow these steps...
- 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.
- 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.
- 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.
- 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.
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.
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
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.