Combine multiple workbooks with VBA

This is the centrepiece, and the macro analysts reach for most. It always follows the same five steps, and getting the error handling right is what separates a toy from a tool:

1. Confirm the folder exists and find its files, with the Dir pattern or FSO.
2. Set the speed settings (ScreenUpdating = False, Calculation = xlManual, EnableEvents = False) before opening anything.
3. For each file, open it, read its range, append to the master, then close it SaveChanges:=False. Guard each open with error handling so one bad file can't abort the run or leave itself open.
4. Restore the speed settings, in an error handler too, so a crash can't leave Excel with the screen frozen and events off.
5. Report how many files and rows went through, plus the grand total.

Sub ConsolidateBranches()
    Dim srcFolder As String, fname As String
    Dim wbSrc As Workbook, wsMaster As Worksheet
    Dim nextRow As Long, filesDone As Long

    srcFolder = ThisWorkbook.Path & "\branches"
    If Dir(srcFolder, vbDirectory) = "" Then
        MsgBox "Folder not found: " & srcFolder, vbCritical: Exit Sub
    End If

    Set wsMaster = ThisWorkbook.Worksheets("Master")
    wsMaster.Range("A2:D100000").ClearContents
    nextRow = 2

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    On Error GoTo CleanFail

    fname = Dir(srcFolder & "\*.xlsx")
    Do While fname <> ""
        Set wbSrc = Workbooks.Open(srcFolder & "\" & fname, ReadOnly:=True)
        Dim rng As Range, r As Range
        Set rng = wbSrc.Worksheets(1).Range("A2:C" & _
                  wbSrc.Worksheets(1).Cells(wbSrc.Worksheets(1).Rows.Count, "A").End(xlUp).Row)
        For Each r In rng.Rows
            wsMaster.Cells(nextRow, 1).Value = Left(fname, InStrRev(fname, ".") - 1) ' branch = file name
            wsMaster.Cells(nextRow, 2).Value = r.Cells(1, 1).Value   ' Product
            wsMaster.Cells(nextRow, 3).Value = r.Cells(1, 2).Value   ' Units
            wsMaster.Cells(nextRow, 4).Value = r.Cells(1, 3).Value   ' Revenue
            nextRow = nextRow + 1
        Next r
        wbSrc.Close SaveChanges:=False
        Set wbSrc = Nothing        ' clear the ref so a later failed Open can't re-close a dead object
        filesDone = filesDone + 1
        fname = Dir()
    Loop

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    MsgBox filesDone & " files consolidated, " & (nextRow - 2) & " rows.", vbInformation
    Exit Sub
CleanFail:
    If Not wbSrc Is Nothing Then wbSrc.Close SaveChanges:=False   ' never orphan an open file
    MsgBox "Failed on " & fname & ": " & Err.Description, vbCritical
    Resume CleanUp
End Sub

How does the error handler work in this macro?

Read the error handling twice. It's only a few lines, and they protect everything else. CleanFail closes any workbook that was open when the crash hit (If Not wbSrc Is Nothing) and reports which file broke it. Then Resume CleanUp jumps to the same restoration code the happy path uses, so the speed settings are restored once and only once, whether the run succeeded or failed. It's the error pattern from VBA II, and here it does real work. Without it, one corrupt branch file leaves you with a frozen screen, manual calc, events off, and an invisible workbook still open in memory.