How To Consolidate Data From Multiple Workbooks In Excel VBA

There are two scenarios for consolidating data from multiple Excel workbooks using vba.

1. You have complete control over the files to be consolidated or merged.

   This could be because you’re starting from scratch and developing a system to be used by your team going forward.

2. You don’t have control over the input files (spreadsheets) to the consolidation process.

   Perhaps the input spreadsheets have existed for quite some time and each department has its own way of doing things.

Scenario 1 above is the easiest way of handling things, as you control the inputs

Here with careful planning and design you can put together an ideal solution easily and relatively quickly.

This is because since you created the Excel files, you can employ good design practices such as separating the data from the display of the data.

Ways of doing this include having a tab for data in a rectangular “data table” format – headers and data and other tabs to display that data.

Also never base a summary report on a detailed report, all reports should point to the data sheet, this minimizes the possibility of errors).

You can then distribute your consolidation templates to your client departments and use various process to consolidate on a regular basis such as have them email Excel files to you or have a shared folder for this purpose created on your local area network.

Scenario 2 where you don’t have control, this is a little trickier.

Here you must be creative as there are many ways to solve the consolidation problem, I go through a few of them in the YouTube video attached to this page.

If you have the support of the people who work on the spreadsheets, then you can create custom export tabs in their Excel files, perhaps organized into predefined data ranges.

When you run your consolidation routines then your code will know where to go to grab the data.

You could also have a configuration worksheet in your Excel Consolidation macro workbook which lists all files to be consolidate and what range to grab from each one.

The attached video tutorial goes into some of these scenario’s in more detail.