Here's a concept that you can look at. I saved them all in a single folder, but not necessary, just easier
I just created some dummy Source workbooks, and used a pivot table to display the results
My Source WB is formatted a little differently
Ask questions if you want
Option Explicit
Sub PackData()
Dim wbMaster As Workbook, wbSource As Workbook
Dim wsSource As Worksheet, wsAll As Worksheet
Dim sFileSource As String
Dim rowMaster As Long, rowSource As Long, colSource As Long
Dim dataSource As Range
'init
Set wbMaster = ThisWorkbook
Application.ScreenUpdating = False
'delete old Master
On Error Resume Next
Application.DisplayAlerts = False
wbMaster.Worksheets("All Projects").Delete
Application.DisplayAlerts = True
On Error GoTo 0
'add new master
wbMaster.Worksheets.Add
Set wsAll = ActiveSheet
wsAll.Name = "All Projects"
rowMaster = 1
wsAll.Cells(rowMaster, 1).Value = "Project"
wsAll.Cells(rowMaster, 2).Value = "Data"
wsAll.Cells(rowMaster, 3).Value = "Value"
rowMaster = rowMaster + 1
sFileSource = Application.GetOpenFilename("Source Files, *.xlsx")
Do While sFileSource <> "False"
Workbooks.Open sFileSource
Set wbSource = ActiveWorkbook
For Each wsSource In wbSource.Worksheets
Set dataSource = wsSource.Cells(1, 1).CurrentRegion
For rowSource = 2 To dataSource.Rows.Count
For colSource = 1 To dataSource.Columns.Count
If Len(dataSource.Cells(rowSource, colSource).Value) > 0 Then
wsAll.Cells(rowMaster, 1).Value = wsSource.Name
wsAll.Cells(rowMaster, 2).Value = dataSource.Cells(1, colSource).Value
wsAll.Cells(rowMaster, 3).Value = dataSource.Cells(rowSource, colSource).Value
rowMaster = rowMaster + 1
End If
Next colSource
Next rowSource
Next
wbSource.Close False
wbMaster.Activate
sFileSource = Application.GetOpenFilename("Source Files, *.xlsx")
Loop
wsAll.Cells(1, 1).CurrentRegion.Name = "AllProjects"
Worksheets("Summary").PivotTables(1).PivotCache.Refresh
Application.ScreenUpdating = True
End Sub