How to Consolidate Data from Multiple Excel Files with VBA in One Click

Consolidating data from multiple Excel files manually can be tedious and time-consuming. But with VBA, you can automate this process and get the job done in one click!

In this tutorial, I’ll guide you through using VBA to merge data from several Excel files into a single worksheet.

By the end of this post, you’ll be able to open multiple Excel files, extract specific data, and consolidate it into one master sheet automatically. Let’s get started.

Setting Up VBA in Excel

If you’ve never used VBA in Excel before, follow these steps to get started:

1. Enable the Developer Tab:
   • Open Excel, go to the File tab, and select Options.
   • Under Customize Ribbon, check the box for Developer and click OK.
2. Access the VBA Editor:
   • Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
   • From here, you can create a new module or use an existing one to write your code.
3. Insert a Command Button:
   • In the Developer Tab, click on Insert, then choose Command Button.
   • Draw the button on your worksheet and double-click it to add your VBA code.

Breaking Down the Code (Line-by-Line)

1. Option Explicit

This line forces you to declare all variables before using them. It helps prevent errors like mistyping a variable name.

2. Private Sub CommandButton1_Click()

This line begins the subroutine that triggers when you click the command button. As soon as you click, the code starts executing the automation process.

3. Declaring Variables

Dim i As Long
Dim lcurrow As Long
Dim lrow As Long
Dim wb As Workbook

• i keeps track of which file is being processed in the loop.
• lcurrow keeps track of the current row in the master worksheet where the data will be pasted.
• lrow keeps track of the current row in the source file being processed.
• wb represents the workbook that is opened during each iteration.

4. For Loop for File Processing

For i = 1 To 5

This loop runs five times, corresponding to the five Excel files you want to consolidate. The code uses i to process the files named Day1.xlx to Day5.xlx.

5. Opening Each Workbook

Set wb = Workbooks.Open("C:\User\Micheal\Desktop\Data\Day" & i & ".xlx")

Here, the code opens each Excel file one by one by combining the file path with the current value of i.

6. Working within Sheet1

With wb.Sheets("Sheet1")

Next, the code tells Excel to work with “Sheet1” of the currently opened workbook. This ensures all subsequent actions refer to that specific sheet.

7. Setting the Starting Row

If i = 1 Then
    lrow = 1
Else
    lrow = 2
End If

For the first file, the code starts copying from row 1. However, for all other files, it starts from row 2 to skip the header row.

8. Looping Until the Last Row of Data

Do Until .Range("A" & lrow).Value = vbNullString

At this point, the loop runs until it encounters an empty cell in column A. It will continue processing data until there are no more rows with values.

9. Incrementing the Current Row in the Master Worksheet

lcurrow = lcurrow + 1

Here, the code increases the value of lcurrow by 1. This ensures that data is pasted into the next available row in the master worksheet.

10. Copying Data from Columns A to D

For n = 0 To 3 Step 1
    Me.Range("A" & lcurrow).Offset(columnoffset:=n).Value = .Range("A" & lrow).Offset(columnoffset:=n).Value
Next n

In this section, the inner loop copies data from columns A to D in the source file and pastes it into the master worksheet. The Offset function ensures the data is pasted into the correct columns.

11. Moving to the Next Row in the Source File

lrow = lrow + 1

After copying the data from a row, the code increases lrow by 1 to move to the next row in the source file.

12. Closing the Workbook

wb.Close True

Once the loop finishes processing the rows in the current file, the code closes the workbook and saves any changes.

13. Advancing to the Next File

Next i

At this point, the loop moves to the next file, incrementing the loop counter i. This allows the code to open and process the next file (Day2.xlx, Day3.xlx, etc.).

14. Cleanup

Set wb = Nothing
End Sub

Finally, the code sets the workbook object to Nothing, releasing any resources it used during the process. The subroutine then ends.

Full Code Example

Here’s the complete VBA code that consolidates data from five Excel files into one worksheet:

Option Explicit

Private Sub CommandButton1_Click()
Dim i As Long
Dim lcurrow As Long
Dim lrow As Long
Dim wb As Workbook

For i = 1 To 5
    Set wb = Workbooks.Open("C:\User\Micheal\Desktop\Data\Day" & i & ".xlx")
    With wb.Sheets("Sheet1")
        If i = 1 Then
            lrow = 1
        Else
            lrow = 2
        End If

        Do Until .Range("A" & lrow).Value = vbNullString
            lcurrow = lcurrow + 1
            For n = 0 To 3 Step 1
                Me.Range("A" & lcurrow).Offset(columnoffset:=n).Value = .Range("A" & lrow).Offset(columnoffset:=n).Value
            Next n
            lrow = lrow + 1
        Loop
    End With
    wb.Close True
Next i

Set wb = Nothing
End Sub

Running the Code

Once you’ve written the code, you can easily run it by clicking the Command Button that you inserted earlier. Here’s how:

1. Open Your Excel Workbook: Make sure that your master workbook is open and ready to consolidate data.
2. Click the Command Button: This will trigger the VBA script, which opens each of the specified files, extracts data, and merges it into your master worksheet.
3. Check the Results: After the code has run, the consolidated data should appear in your master workbook, starting from row 1.

Flow chart of how the code runs

Result: Data Before and After Consolidation

Before running the script, each of your Excel files (Day1.xlx, Day2.xlx, etc.) contains individual data sets in Sheet1.

Before:

After:

• Master Worksheet: Data from Day1.xlx starts from Row 1, and data from Day2.xlx begins immediately after it, and so on.

Conclusion

Automating the process of consolidating data from multiple Excel files using VBA can save you time and effort. With just a few lines of code, you can efficiently combine data from several sources into one master file. This approach is especially useful if you frequently work with similar files or need to manage large data sets across multiple Excel workbooks.

If you enjoyed this tutorial, check out some of our other blog posts, like How to Merge Two or More PDF Files with Python