The separate worksheet ranges can be in the same workbook as the master worksheet or in a different workbook.When you consolidate data, you are assembling it so that you can more easily update and aggregate data regularly or as needed. We then switch back to our main workbook and select the sheet where we want to paste the data, I have assigned this to the variable called “str Where To Copy”.

consolidating data from multiple workbooks-66

I have also made use of UDF (user defined function) to find the last cell in the column that we specify.

This allows us to paste data onto separate sheets within the same workbook.

The following example shows four ranges in cross-tab format.

Data consolidation can use page fields that contain items representing one or more of the source ranges.

For example, you can create formulas with 3D references, or you can use the Consolidate command (on the Data tab, in the Data Tools group).

You can create multiple page fields and assign your own item names for each source range.Common methods to consolidate in Excel include consolidating by position, by category, by formula or by using Excel’s Pivot Table feature.Scroll past the jump to learn how to consolidate in Excel so that your information appears in a master worksheet as a reference whenever you need to generate reports.This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet. Additional things that may be used to enhance this code 1. First, lets take a look at the consolidate data VBA code. Since we are using the same instance of Excel we may allow the user to preserve the format of the data being pasted. Allow the user with the option to clear data before new is pasted. Please Note: You would need to create the data files on your system, this download only contains the code template to consolidate. There is one master file (or sheet) which needs to be consolidated by pulling data from multiple source files containing raw data (having the same data structure). If you are new to VBA, Excel macros, go thru these links to learn more.