Workbook Consolidator

Sub ConsolidateWorkbooks()
    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook
    Dim masterWb As Workbook
    Dim masterWs As Worksheet
    Dim sourceWs As Worksheet
    Dim lastRow As Long

    folderPath = InputBox("Enter folder path containing workbooks to consolidate:")
    If folderPath = "" Then Exit Sub

    ' Create master workbook
    Set masterWb = Workbooks.Add
    Set masterWs = masterWb.Sheets(1)
    masterWs.Name = "Consolidated_Data"

    ' Add headers
    masterWs.Range("A1:D1").Value = Array("Source File", "Data1", "Data2", "Data3")

    fileName = Dir(folderPath & "\*.xlsx")
    lastRow = 2

    Do While fileName <> ""
        Set wb = Workbooks.Open(folderPath & "\" & fileName)
        Set sourceWs = wb.Sheets(1)

        ' Copy data (adjust range as needed)
        sourceWs.Range("A2:C100").Copy
        masterWs.Cells(lastRow, 2).PasteSpecial xlPasteValues

        ' Add source file name
        masterWs.Cells(lastRow, 1).Resize(sourceWs.Range("A2:C100").Rows.Count).Value = fileName

        lastRow = lastRow + sourceWs.Range("A2:C100").Rows.Count
        wb.Close False
        fileName = Dir()
    Loop

    MsgBox "Workbooks consolidated successfully!"
End Sub