How to Consolidate Data from Multiple Excel Workbooks Using VBA

Home More Excel (VBA) Training Videos

One of our website visitors and youtube channel subscriber wanted to know how we can consolidate data from multiple MS Excel workbooks using VBA. The training video shows how you can achieve the consolidation of data using a macro and also automate the process.

The main points of the implementation of the VBA code are given below:
  • First we write the code to display appropriate headers
  • Next we open the workbooks that contain the data
  • Then we access the data in the relevant cells of the array of workbooks and consolidate it and display it in the correct cells in our 'consolidate' workbook
  • Finally we close the workbooks that we opened to access the data

Watch this training video on youtube


The complete video code is given below:
Sub consolidateData()
Range("A1").Select
ActiveCell.Value = "Item"
Range("B1").Select
ActiveCell.Value = "Qty"
Range("A2").Select
ActiveCell.Value = "a"
Range("A3").Select
ActiveCell.Value = "b"
Range("A4").Select
ActiveCell.Value = "c"
Range("B2").Select
Workbooks.Open Filename:="C:\Stock-Sales\a.xlsx"
Workbooks.Open Filename:="C:\Stock-Sales\b.xlsx"
Workbooks.Open Filename:="C:\Stock-Sales\c.xlsx"
Windows("consolidate").Activate
Selection.Consolidate Sources:=Array( _
"'C:\Stock-Sales\[a.xlsx]Sheet1'!R2C2:R4C2", _
"'C:\Stock-Sales\[b.xlsx]Sheet1'!R2C2:R4C2", _
"'C:\Stock-Sales\[c.xlsx]Sheet1'!R2C2:R4C2"), Function:=xlSum
Windows("a.xlsx").Activate
ActiveWorkbook.Close
Windows("b.xlsx").Activate
ActiveWorkbook.Close
Windows("c.xlsx").Activate
ActiveWorkbook.Close
End Sub
Home More Excel (VBA) Training Videos