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.
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.
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