How to Consolidate Data from Multiple Excel Workbooks Using VBA

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:

  1. First we write the code to display appropriate headers
  2. Next we open the workbooks that contain the data
  3. 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
  4. Finally we close the workbooks that we opened to access the data

Watch the video:


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

5 thoughts on “How to Consolidate Data from Multiple Excel Workbooks Using VBA

  1. Pingback: Anonymous

  2. KIRIT PARMAR

    Respected Sir, Thanks for your valuable guidance. May I know about the match criteria. i.e. with help of vlookup I try to do but want to know about a cell data match with criteria follow by row and then column and match with nearby value or higher value.( my query regarding pay structure) .A designation classified on basis of Grade pay. a column specify each grade pay having 40 different pay. A cell value of find first grade pay value(specify in row) and then match value of column.Please help me on this.
    Thanking you.

    Reply
  3. Dipak Borase

    Hi,

    Please help with issue getting error “Type Mistmatch” I have a folder of 10 excel file want to copy C6 and E17 cells from each excel sheet not getting output.

    Note: Also want to select data from specific sheet name please suggest for the same.

    Below code i am using for that please help me with same.

    Sub MergeAllWorkbooks()

    Dim Folderpath As String, Filepath As String, Filename As String

    Folderpath = “C:\Users\Dipak\Desktop\New folder\”

    Filepath = Folderpath & “*.xls”

    Filename = Dir(Filepath)

    Dim Lrow As Long, Lcol As Long

    Do While Filename “”

    Workbooks.Open (Folderpath & Filename)

    Range(Cells(“C6”, “E17”)).Copy
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    ActiveSheet.Paste Destination:=Worksheets(“Sheet1”).Range(Cells(erow, 1), Cells(erow, 2))

    Filename = Dir

    Loop

    Application.DisplayAlerts = True

    End Sub

    Thanks in advance

    Regards,
    Dipak Borase

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *