Consolidating data multiple excel files

30-Oct-2017 14:25

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. Sub Get Data() Dim str Where To Copy As String, str Start Cell Col Name As String Dim str List Sheet As Stringstr List Sheet = “List” On Error Go To Err H Sheets(str List Sheet). Select ‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet Set current WB = Active Workbook Do While Active Cell. If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. Inside our loop are the 4 variables which are assigned the 1) File name, 2) Copy Range, 3) Where To Copy and 4) Which Column contains the starting cell to paste data. Once we have our first data workbook open, we assign this to the data WB variable so that we can easily switch between the two workbooks and close them when the operation has been completed.

Data consolidation in Microsoft Excel is arguably one of the most challenging and time-consuming tasks.

Choose Data Consolidate to view the Consolidate dialog.

Here you will select the function to analyze your data and the references or ranges that you want to consolidate.

Merging multiple Excel files into one allows you to easily compare data, look through the results and create summary reports in Microsoft Excel 2010, 20.

Our wizard will help you summarize, analyze data or prepare a report by joining multiple records and creating a consolidation table.

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”.