Merge multiple excel files into one with multiple sheets

**madmanmarz** · 05-22-2012, 11:21 PM

I originally had an excel file with 6 sheets and two of them were most important. I needed to enter data for 100 items in my inventory which come in sets of two. To make entering the data and printing easier, I made two folders and put one sheet in one folder and one sheet in the other folder and made 100 copies because I had to enter data for 200 items (100 for folder a, 100 for folder b).

So I ended up with:
Folder A - XXX(001) - XXX(100)
Folder B - YYY(001) - YYY(100)

I now realize that I need to get that data back into the original file with the 6 sheets. I would like to have 100 of the original file, with all the files from folder a and b merged in or as new sheets or whatever works. I have found that I can do this manually by opening all the files, and copying them over the original sheets, but of course I have to do this 100 times and I am looking for a way to do this as a batch operation.

Any help would be greatttttly appreciated.

-Mark

**JBeaucaire** · 05-23-2012, 09:29 AM

'WORKBOOKS TO 1 SHEET STACKED
Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.

I'd imagine you'd run a version of this for each folder targeted to a different consolidation sheet each.

**madmanmarz** · 05-23-2012, 09:56 AM

I am confusored.

**JBeaucaire** · 05-23-2012, 10:10 AM

This is VBA. It's not for the faint of heart.

You'll need to install the macro on that page I linked you to into your consilidation workbook.

How/Where to install the macro:

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. You must EDIT the macro for your environment, the parts in red on the web site are the key items. Set the name of the sheet in your workbook where you will collect the values for this version of the macro, and set the fPath to the correct path to that folder.
5. Get out of VBA (Press Alt+Q)
6. Save your workbook

Repeat the steps above to create a SECOND macro to import the values from the other folder onto a different sheet, be sure to change the name of that second macro.

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