Consolidating workbooks in excel
To begin consolidating the sheets in the same workbook, you select a new worksheet to hold the consolidated data.
(If need be, insert a new sheet in the workbook by clicking the Insert Worksheet button.) To begin consolidating sheets in different workbooks, open a new workbook.
For our example we’re going to assume that we have four (or more) Excel files which you can download here.
I’ve stored in a folder called “Data”, which is a subfolder of the “Combine Workbooks” folder (more on why I store them in a subfolder a little later.) Each file has a similar structure, which looks like this: Notice that no one has bothered to set up a table style or anything, they are just raw worksheets of data.
Yes, it’s been covered before, (even linked to in the comments of the previous posts,) but I’m going to put my own flavour on it.
By the time we’re done, you’ll see how similar it is to working with non-Excel files.
By default, Excel uses the SUM function to total all the cells in the worksheets that share the same cell references (when you consolidate by position) or that use the same labels (when you consolidate by category).
He would like to combine a certain worksheet (just one) out of each of these workbooks into a new workbook.
(This should be no surprise—macros are designed to make quick work of tedious manual tasks.) The following macro is simple in design; it loops through all the currently open workbooks and for each workbook (except the workbook that contains the macro) copy the sheet named "Sheet1" from that workbook to the workbook containing the code.
If you want the macro to grab a different worksheet than Sheet1, simply change the value of the s Wks Name variable to reflect the worksheet name desired.
For example, you can use the Consolidate command to total all budget spreadsheets prepared by each department in the company or to create summary totals for income statements for a period of several years.
If you used a template to create each worksheet you’re consolidating, or an identical layout, Excel can quickly consolidate the values by virtue of their common position in their respective worksheets.