This is a common scenario. As a data analyst, you have created an Excel template for collating data (for instance, weekly sales records by state), and you have sent it to five other analysts in your organization for their input. They send their input – each in a new workbook!

And now, you have to extract the information from five separate workbooks into one worksheet, efficiently and without mixing any of the entries up.

You wonder – “is there a macro to get this done efficiently and accurately, under 5 minutes?” Yes there is!

The type of data consolidation you want to perform can be of two kinds:

This tutorial will show you how to set up the macro for the first kind, and the second kind will be explained in a subsequent tutorial.

To consolidate data from several workbooks into one worksheet, the starting point is a common template. We have assumed that all the workbooks have the same structure, with the data we wish to import (number of items sold and number of customers) in the same point in all workbooks: Cells C6:C7 in the worksheet named “Sheet1”. If the data you want to consolidate is not in the same template, this macro may not work as well.

Step 1: Save all the workbooks for consolidation in one folder on your workstation. The folder can be in any location (Desktop, Documents, etc), but choose a location that will be easy for you to access.

Step 2: Set up a new workbook to consolidate the results. This workbook will have the same structure as the others, but I have included a new column for states – so that it is clear where each record has been imported from.

Step 3: Open the Visual Basic Editor (Developer Tab – Visual Basic Editor, or the keyboard shortcut Alt+F11), and create a new module (Insert – New Module)

Step 4: Place the code below in the newly created module:

Option Explicit
Sub Consolidation()
Dim CurrentBook As Workbook
Dim WS As Worksheet
Dim IndvFiles As FileDialog
Dim FileImp, emptyRow As Long
'STEP 1: CREATE A DIALOG BOX TO SELECT WORKBOOKS TO CONSOLIDATE
Set IndvFiles = Application.FileDialog(msoFileDialogOpen)
With IndvFiles
.AllowMultiSelect = True
.Title = "Select workbook for consolidation"
.ButtonName = ""
.Show
End With
'STEP 2: TURN OFF SCREEN UPDATES TO REDUCE SCREEN FLICKERS DURING THE CONSOLIDATION PROCESS
Application.ScreenUpdating = False
'STEP 3: ASSIGN THE SHEET CONTAINING THE CONSOLIDATION TABLE TO A WORKSHEET VARIABLE FOR EASE OF CODE WRITING
Set WS = ThisWorkbook.Sheets("Consolidation sheet")
'STEP 4: CREATE A FOR-NEXT LOOP TO WORK THROUGH EACH WORKBOOK SELECTED IN STEP 1, AND …
For FileImp = 1 To IndvFiles.SelectedItems.Count
'STEP 4A: ...ASSIGN THE WORKBOOK IN VIEW TO A VARIABLE
Set CurrentBook = Workbooks.Open(IndvFiles.SelectedItems(FileImp))
'STEP 4B:...FIND THE NEXT EMPTY ROW ON THE TABLE WHERE THE DATA WILL BE CONSOLIDATED
 WS.Activate
 WS.Range("A1").Select
    Do Until IsEmpty(ActiveCell)
        ActiveCell.Offset(1, 0).Select
    Loop
emptyRow = ActiveCell.Row
'STEP 4C: COPY AND PASTE THE STATE NAME IN THE FOCUS WORKBOOK (CurrentBook) TO THE 1ST COLUMN OF THE NEXT AVAILABLE ROW
CurrentBook.Sheets("Sheet1").Range("C3").Copy
WS.Cells(emptyRow, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'STEP 4D: COPY AND PASTE THE NUMBER OF ITEMS SOLD IN THE FOCUS WORKBOOK (CurrentBook) TO THE 2ND COLUMN OF THE NEXT AVAILABLE ROW
CurrentBook.Sheets("Sheet1").Range("C6").Copy
WS.Cells(emptyRow, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'STEP 4E: COPY AND PASTE THE NUMBER OF CUSTOMERS IN THE FOCUS WORKBOOK (CurrentBook) TO THE 3RD COLUMN OF THE NEXT AVAILABLE ROW
CurrentBook.Sheets("Sheet1").Range("C7").Copy
WS.Cells(emptyRow, 3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'STEP 5: CLEAR ANY SELECTIONS AND CLOSE THE WORKBOOK IN VIEW
Application.CutCopyMode = False
CurrentBook.Close
Next FileImp
Application.ScreenUpdating = True
End Sub

Step 5: Insert a new button control on the Consolidation sheet worksheet (Developer>Insert>Form Controls), assign the above macro (“Consolidation”) to it, and rename the button to “Consolidate Entries”

Step 6: Click the Consolidate Entries button to run the code.