📎 This article includes 1 downloadable practice file ↓

Twenty regional files land in a folder every month and someone copies them into one sheet by hand. This macro does it in seconds.

How Dir works

Dir("C:\Reports\*.xlsx") returns the first matching file name. Calling Dir again with no arguments returns the next one, and an empty string when there are none left.

The complete macro

Sub CombineFolder()
    Dim folder As String, f As String
    Dim wb As Workbook, master As Worksheet
    Dim nextRow As Long, lastRow As Long

    folder = "C:\Reports\"                        ' keep the trailing \
    Set master = ThisWorkbook.Sheets("Master")
    nextRow = master.Cells(master.Rows.Count, 1).End(xlUp).Row + 1

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    f = Dir(folder & "*.xlsx")
    Do While f <> ""
        Set wb = Workbooks.Open(folder & f, ReadOnly:=True)
        With wb.Sheets(1)
            lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            If lastRow > 1 Then
                .Range("A2:F" & lastRow).Copy master.Cells(nextRow, 1)
                master.Cells(nextRow, 7).Resize(lastRow - 1).Value = f   ' source file name
                nextRow = nextRow + lastRow - 1
            End If
        End With
        wb.Close SaveChanges:=False
        f = Dir()
    Loop

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox "Done: " & nextRow - 2 & " rows in Master", vbInformation
End Sub

What each part does

• ScreenUpdating = False stops Excel redrawing for every file — often 5–10× faster.
• ReadOnly:=True means a file someone else has open will not block the macro.
• Column G records which file each row came from, so you can trace a number back.