How to consolidate different Excel sheets?

[ExcelGebruiker]

Does anyone know how to consolidate different Excel sheets containing the same basic template (i.e. same column headers but with a varying numbers of rows) into one Excel file?

[benishiryo]

hi ExcelGelbruiker, u can do that usin MS Query. upload a file & i can show u printscreens of how it's done

[arlu1201]

Do you have a sample file? This can be done through VBA very easily.

[ExcelGebruiker]

Dear benishiryo, arlu1201,

Thank you for responding to my cry for help.

Attached is a sample file containing 3 sheets. The 2nd and 3rd sheets contain opportunity lists from two different people. The aim is to consolidate their opportunity lists in the 1st sheet. (Note that the length of their opportunity lists will vary depending on how many opportunities they have at any one time.)

This is an example where the sheets are contained within the same workbook. What about if the sheets are contained in different files?

Regards,

ExcelGebruiker

[arlu1201]

If the sheets are in the same workbook, use this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.

If the sheets are in separate files, use this code

Please Login or Register  to view this content.

This code will consolidate all files within a folder. You do not need to open. The code should be saved in the Master file (consolidated file).

[Pepe Le Mokko]

Handy add-in for merging sheets, books, etc http://www.rondebruin.nl/merge.htm

[ExcelGebruiker]

Thanks for your help arlu1201. Works perfectly!