How can I merge multiple workbooks into one?

Question

Say I have a directory with the following files:

• Workbook1.xlsx (Contains Sheet1, Sheet2 and Sheet3)
• Workbook2.xlsx (Contains Sheet4 and Sheet5)
• Workbook3.xlsx (Contains Sheet 6)

How can I create a master workbook that contains all spreadsheets?

i.e., MasterWorkbook.xlsx (Contains Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 and Sheet6)

Thanks,

Ignacio

Answer 1

Ignacio,

There is more than one way to do it, you'll need to pick the one based on what you're trying to achieve.

Method 1 (using data source connection): A little bit more complex, but worth if you have a well defined structure in your files. Is required to name ranges in each sheet. You can obviously name a whole sheet as a range, but as smaller the range, the better performance you'll have.

• Open Workbooks1.sheet1
• Select the whole source data you want

• Name this range (by clicking on the upper-left text space that contains the data address, i.e. A1

  Step 1 is done, you have a named range with the source data you want

• Now, go for the MasterWorkbook

• Data Ribbon (I assume you're using Excel 2007, due to the xlsx files)
• Connections
• Add
• Seach more...
• Connect to a new data source
• DSN ODBC
• Excel Files
• select your Workbook1 file
• Select the range you defined previously
• Finish
• Close

Step 2 is done; you have a data source connection to the data at spreadsheet1

• Click in Existing connections
• Add the source you created

Done, you have a link to the data at worksheet1.sheet1

Method 2 (using cell level relationship): More straightforward, however I believe that's slower and more error prone.

• Open Workbooks1
• Open MasterWorkbook
• On Masterworkbook.Sheet1.A1, type "=" [enter]
• Click on Workbooks1.Sheet1.A1 [enter]
• Take a look on the address generated; it's a reference to the Workbooks1.xlsx
• Remove the $ from $A$1 and drag the formula. Initially for the columns, and then for the rows
• Voilà, you have a relationship between the Workbooks

Notice that you'll only be able to see the data in MasterWorkbook; any change in Workbook cells with pointers will raplace that formula.

Edit: There are other solutions as well, as you can see HERE

Answer 2

Do you want an automatic way to do it (i.e. a macro or similar), or do you only need to do it once?

If you only need to do it once, then you can just move all of the worksheets to the same workbook (file).