VBA Code To Split Multiple Tabs in a Workbook to a New File

[calgary_excel]

Hey everyone,

Im trying to copmlie for a VBA code that will help me split my master excel workbook into multiple worksheets for each department.and its sub departments.
Essentially, the master sheet is designed like
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Dept_1 Summary | Sub Department 1 | ....... |Sub Department 10
</code>Dept_2 Summary | Sub Department 1 | ....... |Sub Department 10 I want to split each department and its subdepartments to another file and save as the department name.

I have created a table with the breakdown of the tabs names that need to be split into its own workbook but am failing at trying to link the basic code to the table.

any help?

 

[sur]

Deparments & sub departments are in the columns.

 

[calgary_excel]

No, the workbook has multiple tabs.
The department and sub-departments are the names of the tabs.

The tab names are for example
29500
DE0001
DE0002
29501
FI002

Essentialy I want to split the workbook with all the tabls into each seperate workbook for each department and break links in formula reference or paste as values while keeping the formatting- such as
29500
DE0001
DE0002

and then another workbook for
29501
fi002

The tab names will never change. i have a table that outlines how i want it being split, each row will be a different workbook[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1180"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD]Dept.[/TD]
[TD]Tab1[/TD]
[TD]Tab2[/TD]
[TD]Tab3[/TD]
[TD]Tab4[/TD]
[TD]Tab5[/TD]
[TD]Tab6[/TD]
[TD]Tab7[/TD]
[TD]Tab8[/TD]
[TD]Tab9[/TD]
[/TR]
[TR]
[TD]29500[/TD]
[TD]DE0001[/TD]
[TD]DE0004[/TD]
[TD]DE0002[/TD]
[TD]DE0003[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29501[/TD]
[TD]FI0002[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29502[/TD]
[TD]AC0001[/TD]
[TD]AC0002[/TD]
[TD]AC0011[/TD]
[TD]AC0003[/TD]
[TD]AC0004[/TD]
[TD]AC0006[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29502.1.11[/TD]
[TD]AC0001[/TD]
[TD]AC0011[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29502.2[/TD]
[TD]AC0002[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29503[/TD]
[TD]HR0001[/TD]
[TD]HR0002[/TD]
[TD]HR0003[/TD]
[TD]HR0005[/TD]
[TD]HR0007[/TD]
[TD]HR0008[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29504[/TD]
[TD]PA0002[/TD]
[TD]PA0003[/TD]
[TD]PA0004[/TD]
[TD]PA0007[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29505[/TD]
[TD]IT0001[/TD]
[TD]IT0002[/TD]
[TD]IT0003[/TD]
[TD]IT0004[/TD]
[TD]IT0005[/TD]
[TD]IT0006[/TD]
[TD]IT0007[/TD]
[TD]IT0008[/TD]
[TD]IT0009[/TD]
[/TR]
[TR]
[TD]29506[/TD]
[TD]SS0001[/TD]
[TD]SS0002[/TD]
[TD]SS0004[/TD]
[TD]SS0003[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29507[/TD]
[TD]LC0001[/TD]
[TD]LC0002[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29508[/TD]
[TD]FA0002[/TD]
[TD]FA0007[/TD]
[TD]FA0001[/TD]
[TD]FA0015[/TD]
[TD]FA0014[/TD]
[TD]FA0011[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]29509[/TD]
[TD]AD0005[/TD]
[TD]AD0009[/TD]
[TD]AD0010[/TD]
[TD]AD0014[/TD]
[TD]AD0018[/TD]
[TD]AD0020[/TD]
[TD]AD0021[/TD]
[TD]AD0027[/TD]
[TD]CONT[/TD]
[/TR]
[TR]
[TD]JOB[/TD]
[TD]JOB[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am at my wits end.