Merge Workbooks to Master Workbook

[Excel Fox]

Don, took a while, but here's the code you need

Option Explicit

Sub Consolidate()

    Const clngWorkbooksToConsolidate As Long = 4
    Dim var(1 To clngWorkbooksToConsolidate) As Variant
    Dim varColIndex(1 To clngWorkbooksToConsolidate) As Variant
    Dim varColHeader As Variant
    Dim lngLoop As Long, lngRow As Long, lngCol As Long, lngIndex As Long
    Dim objDic As Object
    Dim strFilesFolder As String
    Dim strFile As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = True
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx", 1
        .Show
        Application.ScreenUpdating = False
        If .SelectedItems.Count = clngWorkbooksToConsolidate Then
            For lngLoop = 1 To clngWorkbooksToConsolidate
                With Workbooks.Open(.SelectedItems(lngLoop), , False)
                    var(lngLoop) = .Sheets(1).Cells(1).CurrentRegion.Value2
                    .Close 0
                End With
            Next lngLoop
        Else
            MsgBox clngWorkbooksToConsolidate & " workbooks were not selected. Program will now exit", vbOKOnly + vbInformation, ""
            GoTo EndSub
        End If
    End With
    Set objDic = CreateObject("Scripting.Dictionary")
    varColHeader = Array("Project Number", "Project Description 1", "Project Description 2", "Project Description 3", "Project Description 4", "Priority Status", "Process approval status", "Project Manager", "Planning responsible", "Customer", "Profit Center")
    For lngLoop = LBound(varColHeader, 1) To UBound(varColHeader, 1)
        objDic.Item(varColHeader(lngLoop)) = 0
    Next lngLoop
    For lngLoop = 1 To clngWorkbooksToConsolidate
        For lngCol = LBound(var(lngLoop), 2) To UBound(var(lngLoop), 2)
            objDic.Item(var(lngLoop)(1, lngCol)) = 0
        Next lngCol
    Next lngLoop
    varColHeader = objDic.keys
    objDic.RemoveAll
    For lngLoop = 1 To clngWorkbooksToConsolidate
        For lngRow = 1 + LBound(var(lngLoop)) To UBound(var(lngLoop))
            objDic.Item(var(lngLoop)(lngRow, 1)) = 0
        Next lngRow
    Next lngLoop
    With ThisWorkbook.Sheets("Master Workbook")
        .UsedRange.ClearContents
        .Cells(1).Resize(, 1 + UBound(varColHeader)).Value = varColHeader
        .Cells(2, 1).Resize(objDic.Count).Value = Application.Transpose(objDic.keys)
        For lngIndex = 1 To clngWorkbooksToConsolidate
            For lngCol = 1 + LBound(var(lngIndex), 2) To UBound(var(lngIndex), 2)
                For lngLoop = 2 To 1 + UBound(varColHeader)
                    If var(lngIndex)(1, lngCol) = .Cells(1, lngLoop).Value Then
                        varColIndex(lngIndex) = varColIndex(lngIndex) & lngLoop & "|"
                    End If
                Next lngLoop
            Next lngCol
        Next lngIndex
        For lngLoop = 1 To clngWorkbooksToConsolidate
            For lngIndex = 2 To objDic.Count
                For lngRow = 2 To UBound(var(lngLoop))
                    If .Cells(lngIndex, 1).Value = var(lngLoop)(lngRow, 1) Then
                        For lngCol = LBound(Split(varColIndex(lngLoop), "|")) To UBound(Split(varColIndex(lngLoop), "|")) - 1
                            .Cells(lngRow, CLng(Split(varColIndex(lngLoop), "|")(lngCol))).Value = var(lngLoop)(lngRow, 2 + lngCol)
                        Next lngCol
                    End If
                Next lngRow
            Next lngIndex
        Next lngLoop
        .UsedRange.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlYes
    End With
    Erase var
    Erase varColHeader
    Erase varColIndex
EndSub:
    Application.ScreenUpdating = True
    Set objDic = Nothing
    
End Sub

I've also attached the workbook with the code. Just press CTRL+SHIFT+R (I've assigned that shortcut to the macro). It will then ask for the 4 workbooks to be selected. Select all 4, and click OK.

Let me know if you face any problem