Linking Excel Workbooks to One Master for Budget..

[Kallen]

I am currently working on a budget, and have 14 different workbooks, that represent 14 different cost centers.

I need to roll up all of these individual worbooks, into one master.

Normally, I do this by creating "Paste Links". Problem with this is, these workbooks are really large and complicated, so this is cumbersome and memory intensive.

Does anyone know if there is any VBA code or add-ins out there to accomplish this task.

Thanks

 

[JVFriederick]

1) Assuming each of the 14 workbooks are built in a consistent fashion, link all cells to one workbook into a table, then copy the links and use search/replace to modify the new filenames. Hint : if your filenames are simple and not "all over the place", this solution works well. Make sure the first set of links are absolute references so that during the copy the rows and columns stay put.

2) If your workbooks are "inconsistent", build a table in each one, perhaps with a new sheet, that refers to the correct summary information you wish to bring over to the master. Then complete step 1.

3) Keep in mind that when you open the completed master, Excel will read all 14 workbooks from the hard disk. So if the budget files are large, well, it can take a little while.

Hope this helps.

 

[DaleWatson123321]

Hi Kallen,

JV's suggestion is one good option, but depending on your application, the following option might also be worth considering:

In the past, I developed and managed a similar application wherein I merged the financial data from 25 different cost centers.

This process involved VBA code which opened each cost-center file (one at a time), and copied and pasted the data sequentially into one "datasheet" (and then each cost-center file was closed, before opening the next cost-center file). As each cost-center's data was merged, the cost center's NAME was entered at the top of that block of data.

The benefit of this process, is that ALL the data from the cost centers is merged into ONE sheet, from where it can be: 1) Printed as ONE report, 2) Analyzed - either by "filtering-in-place" or "extracting selective data to other reports, or using "database" formulas to provide specific reports based on selective criteria.

The feasibility of this option naturally relies upon the "fields" of each cost center being the SAME ...for example a cost-breakdown by Month and/or by Quarter. However, it is expected that the cost-center "appropriation codes" will be different for each cost-center. Another way of describing this is that the "WIDTH" of the data MUST be the SAME, but the "HEIGHT" of the data will (can) vary for each cost-center.

If you decide that this option is worth considering, I could email you the code ...you just have to ask by emailing me and I'll send it by return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca