Attribute VB_Name = "MergeWorkbooks"
Option Explicit

' Combine the first sheet of every Excel file in a folder into one sheet.
' A "Source File" column records where each row came from.
Public Sub MergeWorkbooks()
    Dim folder As String, fileName As String
    Dim master As Worksheet, src As Workbook
    Dim data As Range
    Dim nextRow As Long, rowsCopied As Long, filesDone As Long, sourceCol As Long

    ' 1. Ask the user for a folder
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Choose the folder of workbooks to merge"
        If .Show <> -1 Then Exit Sub
        folder = .SelectedItems(1) & Application.PathSeparator
    End With

    ' 2. Fresh output sheet in this workbook
    Set master = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    master.Name = "Merged " & Format(Now, "yyyy-mm-dd hhmmss")
    nextRow = 1

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' 3. Loop through every .xls / .xlsx / .xlsm file
    fileName = Dir(folder & "*.xls*")
    Do While fileName <> ""
        If fileName <> ThisWorkbook.Name And Left(fileName, 2) <> "~$" Then
            Set src = Workbooks.Open(folder & fileName, ReadOnly:=True)
            Set data = src.Worksheets(1).UsedRange

            If Application.WorksheetFunction.CountA(data) > 0 Then
                ' Header row comes from the first file only
                If nextRow = 1 Then
                    data.Rows(1).Copy master.Cells(1, 1)
                    sourceCol = data.Columns.Count + 1
                    master.Cells(1, sourceCol).Value = "Source File"
                    nextRow = 2
                End If

                ' Copy everything below the header, values only
                If data.Rows.Count > 1 Then
                    With data.Offset(1).Resize(data.Rows.Count - 1)
                        master.Cells(nextRow, 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
                        master.Cells(nextRow, sourceCol).Resize(.Rows.Count, 1).Value = fileName
                        nextRow = nextRow + .Rows.Count
                        rowsCopied = rowsCopied + .Rows.Count
                    End With
                End If
            End If

            src.Close SaveChanges:=False
            filesDone = filesDone + 1
        End If
        fileName = Dir()
    Loop

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    ' 4. Tidy up and report
    master.Rows(1).Font.Bold = True
    master.Columns.AutoFit
    MsgBox "Merged " & rowsCopied & " rows from " & filesDone & " files.", vbInformation
End Sub