combine several spreadsheets into one

**david2005wang** · 06-24-2009, 03:15 PM

I am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?

Many thanks.

**JBeaucaire** · 06-24-2009, 05:26 PM

Here are two simple array macros to consolidate all to one sheet, or parse all to one out to the vendor sheets.

Please Login or Register  to view this content.

How to use the macros:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macros are installed and ready to use. Press Alt-F8 and select them from the macro list.

**david2005wang** · 06-25-2009, 03:34 PM

Hi, JBeaucaire,

Thanks for your programming.

It should be worked I believe. However, when I ran this program either in Debug status or select the Macro in the M.S. spreadsheet, and the spreadsheet popup " 9-0 subscript out of range" and then the program stop.

I trust you have tried in your computer about this program.

by thy way, My M.S is 2003 version.

Any clues will be highly appreciated.

Regards

Wang

**JBeaucaire** · 06-25-2009, 04:54 PM

That error usually means the macro can't find a specific sheet. If you've tried this sheet on a different one that the one you uploaded, it is expecting to find the same sheets.

Try in the attached version of your original file.

The two macros are "Consolidate" and "Parse".

**david2005wang** · 06-26-2009, 12:54 AM

Dear Mr. JBeaucaire, Thanks so munch for you prompt response, really.
What you said is exactly right.
I found the reason that your program don't work for my case.
When I delete the sub-spreadsheets, such as "AEG " "ARP"ARVBEACPMEFXKERMITNUOPTESNDTFS"TTE",
then I run the macro again and click "Parce", the error " 9-0 subscript out of range " popup again; Or, When I delete the "all in on "spreadsheet, and click " consolidate" with the same situiation.

I think there much a way to automactically delete the original, if exist, sub-spreadsheets,and setup new sub-spreadsheets everytime, when I click "Parce", And, automactically delete the original, if exist, "All in one" spreadsheets,and setup new one.

Thanks for you further assisatance.

Regards

P.S. could you leave you email address in order to "bother" you again in the future.

**JBeaucaire** · 06-26-2009, 11:45 AM

Please do not delete the sheets. As stated earlier, it is expected that those sheets will be there. The macros clear away the old data on those sheets, so you do not need to delete them.

Consolidate "clears the data from the All in one sheet" without deleting the sheet itself. This way we don't have to recreate headers and stuff.

Parse clears the individual sheets of data without deleting the sheets and then parses out the data from ALL IN ONE back out to all the sheets.