📎 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

“`visual-basic
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.