Need Macro file to split then consolidate workbooks based on partial file name

[dawnmau]

Hi Fellow Excel Power Users!

As I am a long way from being a guru, I am hoping that someone will be able to assist me in my request.
I want to take the “Split Workbook” macro a step further by doing a "split and consolidate", but not sure how go about doing this.

Here is the details
I have an original report: Central_Rgn_Production_Reports
It contains the following worksheets:

IA _ ASM Production Summary
IA _ ASM Production Detail
IA _ ASM Agent Data
IA _ ASM LOB Grp Chart
MO E ASM Production Summary
MO E ASM Production Detail
MO E ASM Agent Data
MO E ASM LOB Grp Chart
NDSD ASM Production Summary
NDSD ASM Production Detail
NDSD ASM Agent Data
NDSD ASM LOB Grp Chart

I am able to run the following macro to split the worksheets into separate workbooks but I need one that will also consolidate

I would like to consolidate “similar” workbooks into one workbook based on the first 9 characters of the file name and have the workbook named the first 9 characters “ Sales Production Reports”
Examples (based on the list provided):

KS _ ASM Sales Production Reports.xlsx

• KS _ ASM Production Summary
• KS _ ASM Production Detail
• KS _ ASM Agent Data
• KS _ ASM LOB Grp Chart

MO E ASM Sales Production Reports.xlsx

• MO E ASM Production Summary
• MO E ASM Production Detail
• MO E ASM Agent Data
• MO E ASM LOB Grp Chart

NDSD ASM Sales Production Reports.xlsx

• NDSD ASM Production Summary
• NDSD ASM Production Detail
• NDSD ASM Agent Data
• NDSD ASM LOB Grp Chart

Do I need to do this with 2 separate macros or can I do it with one? I have over 175 “reports” that are involved. Also would it be possible to create a macro that will “call” to all files in a certain location and perform this without having to copy and paste the macro to the original files?
In summary would like to have a “Split and consolidate” file called “ASM Production Reports_Macro.xlsm” that will take the xlsx file produced by SAP Business Objects and saved in a folder (for purposes of this example) called Central Sales Region, break apart and consolidate files based on the first 9 characters. Any and all help is greatly appreciated.

[dawnmau]

ok, I found a macro but I need some help modifying it as I am not sure how do it.

Please Login or Register  to view this content.

I am also attaching the file as well as the result. I still need to modify the worksheet naming conventions, and selecting which files to consolidate based on the first 11 characters ( In review, because I want the sheets in a specific order, I added numbers 1-4 in the worksheet name)
Previous:

KS _ ASM Production Summary is now KS _ ASM 1 Production Summary
KS _ ASM Production Detail is now KS _ ASM 2 Production Detail
KS _ ASM Agent Detail is now KS _ ASM 3 Agent Detail
KS _ ASM LOB Grp Chart is now KS _ ASM 4 Grp Chart

I am not sure why the first 3 sheets (Sheet1, Sheet2, Sheet3) were not deleted. Hope this helps!

[dawnmau]

LOL! Ok I have now figured out how to fix the worksheet name by modifying the macro as follows:

Please Login or Register  to view this content.

Now if I can figure out how to change the part re: the selection of files and naming the workbook based on the first 11 characters of the worksheet name.....

Please Login or Register  to view this content.