So I need a bit of help with an existing macro.
I need to split a workbook's multiple worksheets into multiple files (not based on worksheet name).
The project: It deals with very sensitive HR/performance data, and I need to send 1000s of employees' data to their individual managers (about 100 managers who can only see their team's data, and no one else's), so I need about 100 files split (1 for each manager).
The file: - Many different tabs, separated by role. - First column is a unique identifier made by concatenating the Manager's name with the job title ex. John Stevens_Office Manager
The task: John Stevens will have team members in many different job roles, and needs all that data in one file, separated into tabs by job role. My current macro does half of this (splits the file, but does not unite).
It also doesn't delete out the other tabs from the file...and its a big file with about 50 tabs. Even just some help deleting the other tabs would be greatly appreciated. Also, the data is populated via VLookup, and every time it splits a file it gives me a message asking if I want to update the links? Can the updates be turned on permanently so it splits without any manual input?
Below is some sample data. Please keep in mind that the actual file is far more complex (at least 50 columns)
Sample Data
Tab 1: Office Manager
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Identifier[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Performance[/TD]
[/TR]
[TR]
[TD]John Stevens_Office Manager[/TD]
[TD]Killjoy[/TD]
[TD]Heidi[/TD]
[TD]8/10[/TD]
[/TR]
[TR]
[TD]Lindsay Brown_Office Manager[/TD]
[TD]Wilcox[/TD]
[TD]Tommy[/TD]
[TD]9/10[/TD]
[/TR]
[TR]
[TD]Tom Fields_Office Manager[/TD]
[TD]Thorne[/TD]
[TD]Ronald[/TD]
[TD]7/10[/TD]
[/TR]
</tbody>[/TABLE]
Tab 2: Office Coordinator
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Identifier[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Performance[/TD]
[/TR]
[TR]
[TD]John Stevens_Office Coordinator[/TD]
[TD]Shields[/TD]
[TD]Betty[/TD]
[TD]7/10[/TD]
[/TR]
[TR]
[TD]Lindsay Brown_Office Coordinator[/TD]
[TD]Johnson[/TD]
[TD]Craig[/TD]
[TD]9/10[/TD]
[/TR]
[TR]
[TD]Tom Fields_Office Coordinator[/TD]
[TD]Corgan[/TD]
[TD]Billy[/TD]
[TD]10/10[/TD]
[/TR]
</tbody>[/TABLE]
Tab 3: AR Associate
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Identifier[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Performance[/TD]
[/TR]
[TR]
[TD]John Stevens_AR Associate[/TD]
[TD]Spears[/TD]
[TD]Britney[/TD]
[TD]4/10[/TD]
[/TR]
[TR]
[TD]Lindsay Brown_AR Associate[/TD]
[TD]Cobain[/TD]
[TD]Kurt[/TD]
[TD]10/10[/TD]
[/TR]
[TR]
[TD]Tom Fields_AR Associate[/TD]
[TD]Wilson[/TD]
[TD]Brian[/TD]
[TD]9/10[/TD]
[/TR]
</tbody>[/TABLE]
Based on that sample data, the ideal macro would give me 3 files with 3 worksheets in each, and 1 row of data in each worksheet. Ideally, the file name would just be the manager's name and the worksheets' names would be the job titles.
Thanks,
Mark
I need to split a workbook's multiple worksheets into multiple files (not based on worksheet name).
The project: It deals with very sensitive HR/performance data, and I need to send 1000s of employees' data to their individual managers (about 100 managers who can only see their team's data, and no one else's), so I need about 100 files split (1 for each manager).
The file: - Many different tabs, separated by role. - First column is a unique identifier made by concatenating the Manager's name with the job title ex. John Stevens_Office Manager
The task: John Stevens will have team members in many different job roles, and needs all that data in one file, separated into tabs by job role. My current macro does half of this (splits the file, but does not unite).
It also doesn't delete out the other tabs from the file...and its a big file with about 50 tabs. Even just some help deleting the other tabs would be greatly appreciated. Also, the data is populated via VLookup, and every time it splits a file it gives me a message asking if I want to update the links? Can the updates be turned on permanently so it splits without any manual input?
Below is some sample data. Please keep in mind that the actual file is far more complex (at least 50 columns)
Sample Data
Tab 1: Office Manager
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Identifier[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Performance[/TD]
[/TR]
[TR]
[TD]John Stevens_Office Manager[/TD]
[TD]Killjoy[/TD]
[TD]Heidi[/TD]
[TD]8/10[/TD]
[/TR]
[TR]
[TD]Lindsay Brown_Office Manager[/TD]
[TD]Wilcox[/TD]
[TD]Tommy[/TD]
[TD]9/10[/TD]
[/TR]
[TR]
[TD]Tom Fields_Office Manager[/TD]
[TD]Thorne[/TD]
[TD]Ronald[/TD]
[TD]7/10[/TD]
[/TR]
</tbody>[/TABLE]
Tab 2: Office Coordinator
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Identifier[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Performance[/TD]
[/TR]
[TR]
[TD]John Stevens_Office Coordinator[/TD]
[TD]Shields[/TD]
[TD]Betty[/TD]
[TD]7/10[/TD]
[/TR]
[TR]
[TD]Lindsay Brown_Office Coordinator[/TD]
[TD]Johnson[/TD]
[TD]Craig[/TD]
[TD]9/10[/TD]
[/TR]
[TR]
[TD]Tom Fields_Office Coordinator[/TD]
[TD]Corgan[/TD]
[TD]Billy[/TD]
[TD]10/10[/TD]
[/TR]
</tbody>[/TABLE]
Tab 3: AR Associate
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Identifier[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Performance[/TD]
[/TR]
[TR]
[TD]John Stevens_AR Associate[/TD]
[TD]Spears[/TD]
[TD]Britney[/TD]
[TD]4/10[/TD]
[/TR]
[TR]
[TD]Lindsay Brown_AR Associate[/TD]
[TD]Cobain[/TD]
[TD]Kurt[/TD]
[TD]10/10[/TD]
[/TR]
[TR]
[TD]Tom Fields_AR Associate[/TD]
[TD]Wilson[/TD]
[TD]Brian[/TD]
[TD]9/10[/TD]
[/TR]
</tbody>[/TABLE]
Based on that sample data, the ideal macro would give me 3 files with 3 worksheets in each, and 1 row of data in each worksheet. Ideally, the file name would just be the manager's name and the worksheets' names would be the job titles.
Thanks,
Mark
