Hi all -
I've spent the better part of 3 hours trying to find a solution for this, but I can't seem to figure it out.
I have a workbook that contains 3 tabs:
- (#1) "summary" - contains a scorecard summary of the data in sheet #2, "data"
- (#2) "data" - contains a listing of employees, each EE's manager, and a set of metrics about those employees
- (#3) "data validation" - a hidden sheet that contains a set of data to create a data validation list in one of the columns in sheet #2, and a list that #2 relies on for lookups
End goal:
- I'd like to split this file out into individual workbooks based on the employee manager field in sheet #2, but maintain all three sheets and localize any formulas/data validation lists.
My issues:
- I can't seem to figure out how to copy all three sheets over. Any macro I encounter only copies sheet #2 data.
- I have no idea how to localize any of the formulas that exist in sheet #1 & sheet #2 into the new file. e.g., if the first individual workbook I create is manager xyz, the data validation is still trying to drive off the master list, which breaks anyway, and the rest of the formulas in sheet #1 & #2 are looking back to the master list as well
- BONUS: I'd like for sheet #3 to stay hidden in each workbook, and to protect each of the sheets & the workbook after the new file has been created. Not sure if this is possible, but would save a lot of time if so.
I'm attaching a very generic version of the workbook here. I've taken lot of the formulas & identifying information out, but it should give a good idea of what I'm looking for. Any help is much appreciated - thanks!
Bookmarks