You pick a folder, and the macro opens every Excel file in it (read-only), grabs the first sheet of each, and stacks the rows into a single 'Combined' sheet in this workbook. It keeps the header row from the first file only and adds a 'Source File' column so you can always see where each row came from, then brands the result — the same monthly exports, locations, or entities merged into one dataset you can pivot or report on. Re-running rebuilds the sheet from scratch, so it stays current.

Sub CombineFilesInFolder()
    ' --- Wauvel palette · swap these for your own brand ---
    Const BRAND As String = "Wauvel"
    Dim cAccent As Long, cPop As Long, cBand As Long
    cAccent = RGB(108, 77, 246)   ' header fill   #6C4DF6
    cPop = RGB(214, 248, 76)      ' accent line   #D6F84C
    cBand = RGB(247, 245, 255)    ' zebra stripe  #F7F5FF

    Dim folder As String, file As String
    Dim src As Workbook, ws As Worksheet, master As Worksheet
    Dim firstRow As Long, lastRow As Long, lastCol As Long
    Dim destRow As Long, filesDone As Long
    Dim tagFrom As Long, tagTo As Long, rr As Long, i As Long

    ' 1. Pick the folder of files to combine.
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Pick the folder of Excel files to combine"
        If .Show <> -1 Then Exit Sub
        folder = .SelectedItems(1)
    End With
    If Right$(folder, 1) <> "\" Then folder = folder & "\"

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ' 2. Start a fresh "Combined" sheet so re-runs are clean.
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("Combined").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Set master = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
    master.Name = "Combined"
    destRow = 1

    ' 3. Loop every workbook in the folder (skip this file and Excel temp files).
    file = Dir(folder & "*.xls*")
    Do While file <> ""
        If file <> ThisWorkbook.Name And Left$(file, 1) <> "~" Then
            Application.StatusBar = BRAND & ": combining " & file & " ..."
            Set src = Workbooks.Open(fileName:=folder & file, ReadOnly:=True)
            Set ws = src.Worksheets(1)

            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
            firstRow = IIf(destRow = 1, 1, 2)   ' header from the first file only

            If lastRow >= firstRow Then
                ws.Range(ws.Cells(firstRow, 1), ws.Cells(lastRow, lastCol)).Copy
                master.Cells(destRow, 1).PasteSpecial Paste:=xlPasteValues

                ' Tag each data row with the file it came from.
                If destRow = 1 Then master.Cells(1, lastCol + 1).Value = "Source File"
                tagFrom = IIf(destRow = 1, 2, destRow)
                tagTo = destRow + (lastRow - firstRow)
                For rr = tagFrom To tagTo
                    master.Cells(rr, lastCol + 1).Value = file
                Next rr

                destRow = master.Cells(master.Rows.Count, 1).End(xlUp).Row + 1
            End If

            src.Close SaveChanges:=False
            filesDone = filesDone + 1
        End If
        file = Dir
    Loop
    Application.CutCopyMode = False

    ' 4. Brand the header row and zebra-stripe the body.
    lastRow = master.Cells(master.Rows.Count, 1).End(xlUp).Row
    lastCol = master.Cells(1, master.Columns.Count).End(xlToLeft).Column
    If filesDone > 0 And lastRow >= 1 Then
        With master.Range(master.Cells(1, 1), master.Cells(1, lastCol))
            .Interior.Color = cAccent
            .Font.Color = RGB(255, 255, 255)
            .Font.Bold = True
            .Borders(xlEdgeBottom).Color = cPop
            .Borders(xlEdgeBottom).Weight = xlMedium
        End With
        For i = 2 To lastRow
            If i Mod 2 = 0 Then _
                master.Range(master.Cells(i, 1), master.Cells(i, lastCol)).Interior.Color = cBand
        Next i
        master.Columns.AutoFit
    End If

    Application.StatusBar = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    If filesDone = 0 Then
        MsgBox "No .xlsx/.xls files found in that folder.", vbExclamation, BRAND
    Else
        MsgBox "Combined " & filesDone & " file(s) into 'Combined' — " & _
            (lastRow - 1) & " data rows, tagged by source file.", _
            vbInformation, BRAND
    End If
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm) — or download the workbook: a branded Excel file with this code on a sheet, sample data to run it on, and a step-by-step setup guide.

• Consolidate monthly exports (Jan, Feb, Mar…) into one dataset.
• Merge the same report pulled from several locations or entities.
• Stack a pile of Excel/CSV dumps before a PivotTable or a report.

A simulation — press Run to perform what the macro does to a sample workbook.

• Power Query → Get Data → From Folder — The no-code way: point Power Query at the folder and it combines + refreshes on demand. Best when the file list keeps growing.
• Copy / paste by hand — Fine for two or three files; painful and error-prone past that.