Consolidate data from different excel files (VBA)

Consolidate data from different excel files (VBA):
This is a guest post by Vijay, our in-house VBA Expert.
Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.

Consolidate Data Demo

First, lets take a look at the consolidate data VBA code.

Consolidating Data from different Excel files – the setup

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).
Lets try to make a generic consolidation macro so that we can use this almost anywhere.
We start of by creating a simple table on our sheet, we will call this List.

• On this table essentially we are defining everything that our VBA code needs to know to copy and paste data.
• We start by telling the name of the Excel workbook and then the complete path (location) of the file.
• In the next 2 cells we define what are the starting cell and the ending cell that contains our data.
• Next we are put the name of the worksheet where the data will be pasted. In our example the sheet remains the same however as per your requirements you may put a different sheet name.
• The last option is to specify where to paste the copied data and we only need to tell the start cell address, the code will automatically select the next empty cell in that column and then paste the data from that point onwards.

Let’s understand the code.

Sub GetData()

Dim strWhereToCopy As String, strStartCellColName As String

Dim strListSheet As StringstrListSheet = “List”
On Error GoTo ErrH

Sheets(strListSheet).Select

Range(“B2″).Select
‘this is the main loop, we will open the files one by one and copy their data into the masterdata sheet

Set currentWB = ActiveWorkbook

Do While ActiveCell.Value <> “”
strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value

strCopyRange = ActiveCell.Offset(0, 2) & “:” & ActiveCell.Offset(0, 3)

strWhereToCopy = ActiveCell.Offset(0, 4).Value

strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True

Set dataWB = ActiveWorkbook
Range(strCopyRange).Select

Selection.Copy
currentWB.Activate

Sheets(strWhereToCopy).Select

lastRow = LastRowInOneColumn(strStartCellColName)

Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone

Application.CutCopyMode = False

dataWB.Close False

Sheets(strListSheet).Select

ActiveCell.Offset(1, 0).Select

Loop

Exit Sub
ErrH:

MsgBox “It seems some file was missing. The data copy operation is not complete.”

Exit Sub

End Sub

We have used the Workbook object to accomplish this task and also the Error handler to trap any errors that may come in case any file is missing.
The current code will display a message box when it is not able to open any file and will stop.
We start by assigning the workbook where we want to consolidate the date to the variable currentWB by using the statement:
Set currentWB = ActiveWorkbook
After this a looping construct has been used to go through all the inputs provided one by one and open the workbooks, it has been assumed these workbooks to contain on the data that we need to copy hence I did not specify the source sheet name, however this can be easily added to this code to add more functionality.
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.
We open the data workbook by using the Application.Workbooks.Open method.

Once we have our first data workbook open, we assign this to the dataWB variable so that we can easily switch between the two workbooks and close them when the operation has been completed.
Next we select the data that has been assigned to the copy range and copy to the clipboard.
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 “strWhereToCopy”. This allows us to paste data onto separate sheets within the same workbook.
I have also made use of UDF (user defined function) to find the last cell in the column that we specify.
Once we have found the last row we then select the next empty cell below that and paste our data then.
Additional things that may be used to enhance this code
1. Since we are using the same instance of Excel we may allow the user to preserve the format of the data being pasted.

2. Allow the user with the option to clear data before new is pasted.

Download Consolidate Data from different files Demo file

Click here to download the workbook.
Please Note: You would need to create the data files on your system, this download only contains the code template to consolidate.

More on VBA & Macros

If you are new to VBA, Excel macros, go thru these links to learn more.

• More Examples on Consolidation
• What is VBA & Macros? Introduction
• Excel VBA Example Macros
• VBA tutorial videos

Join our VBA Classes

If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.
Click here to learn more about VBA Classes & join us.