If you routinely receive separate weekly or monthly report files from multiple team members, opening each workbook to manually copy and paste rows into a master file is both time-consuming and error-prone.

In this guide, you will learn how to use a powerful Excel VBA macro that automatically prompts you to select a folder and merges data from all Excel files inside it into a single **Master Sheet** with just one click.

---

Why Automate File Consolidation?

Consolidating external files manually is one of the most common productivity bottlenecks in business operations. Automating it gives you:

• Dynamic Folder Selection: Pick any folder on your computer using a clean pop-up window.
• Automatic Header Protection: Retains headers from the first file while skipping duplicate headers from subsequent workbooks.
• Fast Batch Processing: Handles dozens of `.xlsx` files in seconds without manually opening each one.

---

Step 1: Open the VBA Editor Window

1. Open a blank Excel workbook.
2. Press Alt + F11 (Option + F11 on Mac) to open the Visual Basic Editor.
3. In the top menu bar, click Insert ➔ Module to open a clean code window.

---

Step 2: Copy and Paste the File Merge VBA Code

Copy the code block below and paste it directly into your blank module window:

Sub CombineMultipleFiles()
    Dim folderPath As String
    Dim fileName As String
    Dim masterWs As Worksheet
    Dim sourceWorkbook As Workbook
    Dim sourceWs As Worksheet
    Dim lastRow As Long
    Dim masterLastRow As Long
    Dim isFirstFile As Boolean
    Dim fileDialog As FileDialog
    
    ' 1. Allow user to select a folder
    Set fileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    fileDialog.Title = "Select the Folder Containing Excel Files to Merge"
    
    If fileDialog.Show = -1 Then
        folderPath = fileDialog.SelectedItems(1) & "\"
    Else
        MsgBox "No folder selected. Macro canceled.", vbExclamation, "Excel Owl Automation"
        Exit Sub
    End If
    
    ' 2. Optimize execution speed
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' 3. Set up Master Sheet
    Set masterWs = ActiveWorkbook.Sheets(1)
    masterWs.Name = "Master Data"
    masterWs.Cells.Clear
    
    isFirstFile = True
    fileName = Dir(folderPath & "*.xlsx*")
    
    ' 4. Loop through each Excel file in folder
    Do While fileName <> ""
        ' Skip the active workbook if saved in same folder
        If fileName <> ActiveWorkbook.Name Then
            Set sourceWorkbook = Workbooks.Open(folderPath & fileName, ReadOnly:=True)
            Set sourceWs = sourceWorkbook.Sheets(1)
            
            lastRow = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row
            
            If lastRow >= 1 Then
                If isFirstFile Then
                    ' Copy header + data from first file
                    sourceWs.Rows("1:" & lastRow).Copy masterWs.Range("A1")
                    isFirstFile = False
                Else
                    ' Find next blank row in Master and copy data only (skip header)
                    masterLastRow = masterWs.Cells(masterWs.Rows.Count, "A").End(xlUp).Row + 1
                    sourceWs.Rows("2:" & lastRow).Copy masterWs.Range("A" & masterLastRow)
                End If
            End If
            
            sourceWorkbook.Close SaveChanges:=False
        End If
        fileName = Dir
    Loop
    
    ' 5. Auto-fit columns & restore settings
    masterWs.Columns.AutoFit
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox "All Excel files successfully merged into Master Data!", vbInformation, "Excel Owl Automation"
End Sub

---

Step 3: Run the Macro Using Alt + F8

You can trigger this automatic file-merging process straight from your workbook:

1. Switch back to your main Excel window.
2. Press Alt + F8 (or Option + F8 on Mac) to open the Macro dialog.
3. Select CombineMultipleFiles from the list and click Run.
4. A window will pop up—select the folder containing your target Excel files and click OK.

💡 Pro Tip: Folder Selection Shortcut
Make sure all your source files are saved inside a single folder before running the script so VBA can loop through every file smoothly!

---

Step 4: Save as Macro-Enabled Workbook (.xlsm)

To retain your macro code for future file consolidations, save your file in the macro-enabled format:

1. Click File ➔ Save As (or press F12).
2. In the Save as type drop-down, choose Excel Macro-Enabled Workbook (*.xlsm).
3. Click Save.

⚠️ Important Note: Standard .xlsx files cannot store macro scripts. Saving as a normal .xlsx workbook will permanently delete your code!