Excel VBA Macro to Combine Data from Multiple Excel Files into One Master Sheet

 

Do you receive multiple Excel files every day and need to combine their data into a single master workbook? If you perform this task manually, you may spend a lot of time opening files, copying rows, and pasting data one by one.

In this tutorial, we will learn how to use an Excel VBA macro to consolidate data from multiple workbooks automatically. The macro allows you to select multiple Excel files, find the worksheet named Tracker, copy its data, and append the records to a master worksheet named Sheet1.

This method is useful for MIS reporting, monthly production reports, quality analysis, insurance operations, sales reports, and other repetitive Excel tasks.

What Is the Purpose of This VBA Macro?

Imagine that you work as an MIS Executive or Quality Analyst. Every day, you receive tracker files from different team members. Each file contains employee production or quality data in a worksheet named Tracker.

For example, you receive these files:

• Tracker_January.xlsx

• Tracker_February.xlsx

• Tracker_March.xlsx

• Tracker_April.xlsx

Each workbook contains data in columns A to F.

Instead of opening every workbook and copying the data manually, you can run one VBA macro to combine the records into a master workbook.

Example of the Final Output

Suppose your master worksheet contains the following data:

Employee ID	Employee Name	Department	Production	Errors	Quality
E101	Amit	Operations	120	2	98%
E102	Ravi	Quality	135	1	99%
E103	Neha	Operations	110	3	97%
E104	Priya	Quality	140	2	98%

The macro copies the data from each selected tracker file and adds it below the existing records in the master worksheet.

Note: This is sample data for illustration. Your actual column headings and records may be different.

How Does the VBA Code Work?

Before using the complete macro, let's understand its important steps.

Step 1: Declare Variables

The macro uses variables to store the number of selected files, workbook names, and row numbers.

• n stores the number of selected files.

• wb tracks the current file in the loop.

• master stores the name of the master workbook.

• Userfile stores the name of the source workbook.

• l_Row identifies the last used row in the tracker sheet.

• l_Dist identifies the next row in the master sheet.

Step 2: Turn Off Screen Updating and Alerts

Application.DisplayAlerts = False
Application.ScreenUpdating = False

These settings suppress certain Excel alerts and prevent screen refreshing while the macro runs.

This can make the process smoother, especially when several files are involved.

Step 3: Select Multiple Excel Files

With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = True
    .Title = "Locate YourFiles"
    .Show

This opens Excel's file selection dialog, allowing you to choose multiple workbooks in one go.

Step 4: Open Each Selected Workbook

For wb = 1 To n
    Path = .SelectedItems(wb)
    Workbooks.Open (Path)

The loop opens each selected file one by one so the macro can read its data.

Step 5: Find the Tracker Worksheet

For Each Sheet In ActiveWorkbook.Worksheets
    If Sheet.Name = "Tracker" Then
        Sheet.Select

This checks the worksheets in the source workbook and proceeds when it finds a worksheet named Tracker.

Step 6: Find the Last Row and Copy the Data

l_Row = Sheets("Tracker").Range("A1048576").End(xlUp).Row
'this will find the last row of the tracker sheet

Range("A2:F" & l_Row).Copy
'this code will copy all the data from tracker sheet

The first line finds the last used row in column A. The second line copies columns A through F, starting from row 2.

This assumes row 1 contains the headings and column A is populated for each record.

Step 7: Activate the Master Workbook

Windows(master).Activate
'this code will activate the master file where we will paste our data

This switches back to the master workbook so the copied data can be added to the destination worksheet.

Step 8: Find the Next Blank Row

l_Dist = Sheets("Sheet1").Range("A1048576").End(xlUp).Row + 1
'this code will find the next blank row in the master file

Sheets("Sheet1").Range("A" & l_Dist).Select
'this will find the last non blank cell in the master file

The macro checks column A of Sheet1 and identifies the next row after the last used row.

Step 9: Paste the Data and Close the Source Workbook

ActiveSheet.Paste
Windows(Userfile).Close savechanges:=False

The copied data is pasted into the master sheet, and the source workbook is closed without saving any changes.

Complete Excel VBA Code

Copy the following code into a standard VBA module. It combines the code from both parts, retaining the original comments.

Sub Tracker_compiler_1()

Dim n As Integer
Dim wb As Integer
Dim master As String
Dim Userfile As String
Dim l_Row As Long
Dim l_Dist As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

master = ThisWorkbook.Name

With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = True
    .Title = "Locate YourFiles"
    .Show

    n = .SelectedItems.Count

    For wb = 1 To n
        Path = .SelectedItems(wb)
        Workbooks.Open (Path)

        Userfile = ActiveWorkbook.Name

        For Each Sheet In ActiveWorkbook.Worksheets
            If Sheet.Name = "Tracker" Then
                Sheet.Select
                l_Row = Sheets("Tracker").Range("A1048576").End(xlUp).Row
                'this will find the last row of the tracker sheet

                Range("A2:F" & l_Row).Copy
                'this code will copy all the data from tracker sheet

                Windows(master).Activate
                'this code will activate the master file where we will paste our data

                l_Dist = Sheets("Sheet1").Range("A1048576").End(xlUp).Row + 1
                'this code will find the next blank row in the master file

                Sheets("Sheet1").Range("A" & l_Dist).Select
                'this will find the last non blank cell in the master file

                ActiveSheet.Paste

                Windows(Userfile).Close savechanges:=False
            End If
        Next Sheet

    Next wb

End With

End Sub

How to Run the VBA Macro in Excel

Follow these steps to execute the code.

1. Open the Excel workbook that will serve as your master file.

2. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm).

3. Press Alt + F11 to open the Visual Basic Editor.

4. Click Insert → Module.

5. Paste the complete VBA code into the module.

6. Ensure the master workbook contains a worksheet named Sheet1.

7. Press F5 to run the macro.

8. Select the source Excel files that contain the Tracker worksheet.

9. Click Open to begin processing the selected files.

After the macro runs, check Sheet1 in the master workbook to review the consolidated data.