VBA: Loop Through Every File in a Folder (and Combine Them)

📎 This article includes 1 downloadable practice file ↓

Twelve branch files arrive every month and someone copies each into a master sheet. A short macro opens every file in the folder, copies the data under the last row of the master, notes which file each row came from, and closes it again.

Pick the folder

Function PickFolder() As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Choose the folder with the files"
        If .Show = -1 Then PickFolder = .SelectedItems(1) & "\"
    End With
End Function

Loop with Dir

Sub CombineFolder()
    Dim folder As String, f As String, wb As Workbook, master As Worksheet
    Dim lastSrc As Long, nextRow As Long
    folder = PickFolder(): If folder = "" Then Exit Sub
    Set master = ThisWorkbook.Sheets("Master")
    Application.ScreenUpdating = False: Application.DisplayAlerts = False
    f = Dir(folder & "*.xls*")
    Do While f <> ""
        Set wb = Workbooks.Open(folder & f, ReadOnly:=True)
        With wb.Sheets(1)
            lastSrc = .Cells(.Rows.Count, 1).End(xlUp).Row
            If lastSrc > 1 Then
                nextRow = master.Cells(master.Rows.Count, 1).End(xlUp).Row + 1
                .Range("A2:E" & lastSrc).Copy master.Cells(nextRow, 1)
                master.Range(master.Cells(nextRow, 6), master.Cells(nextRow + lastSrc - 2, 6)).Value = f
            End If
        End With
        wb.Close SaveChanges:=False
        f = Dir()
    Loop
    Application.ScreenUpdating = True: Application.DisplayAlerts = True
    MsgBox "Done"
End Sub

Dir(pattern) returns the first matching file; Dir() with no argument returns the next. Column F records the source file name, your audit trail.

Where people go wrong

For a no-code route, Power Query’s combine files from a folder does the same and refreshes.