Copy Paste from multiple worksheets into one based on multiple criteria

[mancubus]

first bit adds two worksheets "master" and "uid", if they dont exist; clears their existing data if they exist. (master is for consolidation and uid is for controlling the number of same persons's records in all 4 sheets (x, y, z, p))
writes column headers from worksheet "data fields" to worksheet "master".
adds headers ("UID", "x", "y", "z", "p") to worksheet "uid".

    On Error Resume Next
    Set wsM = Worksheets("master")
    ...
    ...
    ...
        .Range("A1:E1") = Array("UID", "x", "y", "z", "p")
    End With

second bit creates an id by concatenating cell values in columns C and D (format: FirstName MiddleName LastName_BOD). inserts a blank column as their first column, writes these id's in the corresponding cells of column A of all 4 sheets (x, y, z, p). writes these id's to column A of worksheet "uid" as well. then sorts worksheets on uid's, ie Column A.

    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
    ...
    ...
    ...
        Next e
    End With

the third bit counts the number of records of the same person in all 4 worksheets. this is necessary for covering the all records of the same person. and writes the sorted uid's for the same person maximum (of 4 ws) number of times) in col A of master.

    With wsU
    'determine the number of same uid in all wss
    ...
    ...
    ...
    End With

now we have 6 worksheets having uid's in their column A; with one difference, worksheet "uid" has unique list of uid's.

the last bit finally consolidates the data from 4 sheets (x, y, z, p) in master.

    For Each e In Array("x", "y", "z", "p")
        With Worksheets(e)
    ...
    ...
    ...
        End With
    Next e

remember uid's in worksheet uid are sorted (so are in master)?

loops row 2 to LastRow in 4 wss.
looks for uid in col A of "master" up-down, finds the first occurence of uid, returns its row number as FRuid.
looks for uid in col A of "master" bottom-up, finds the first (last, from up-down perspective) occurence of uid, returns its row number as LRuid.
loops column 2 to LastCol (.Cells(1, .Columns.Count).End(xlToLeft).Column) in 4 wss.
looks for headers in Row 1 of "master" left-right, finds the header, returns its column number as ColNumMaster.
writes the cell value (.Cells(i, j)) from 4 worksheets to master, in the intersect cell (FRuid To LRuid, ColNumMaster), if that cell is not previously inserted a value, according to outer and inner loops.