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

Dept. Tab1 Tab2 Tab3 Tab4 Tab5 Tab6 Tab7 Tab8 Tab9 29500 DE0001 DE0004 DE0002 DE0003 29501 FI0002 29502 AC0001 AC0002 AC0011 AC0003 AC0004 AC0006 29502.1.11 AC0001 AC0011 29502.2 AC0002 29503 HR0001 HR0002 HR0003 HR0005 HR0007 HR0008 29504 PA0002 PA0003 PA0004 PA0007 29505 IT0001 IT0002 IT0003 IT0004 IT0005 IT0006 IT0007 IT0008 IT0009 29506 SS0001 SS0002 SS0004 SS0003 29507 LC0001 LC0002 29508 FA0002 FA0007 FA0001 FA0015 FA0014 FA0011 29509 AD0005 AD0009 AD0010 AD0014 AD0018 AD0020 AD0021 AD0027 CONT JOB JOB <colgroup><col><col span="9"></colgroup><tbody> </tbody>

<tbody>
</tbody>

I am at my wits end.