Develop a master file using data scattered in different sheets of multiple workbooks

Dear Experts

I have 100+ workbooks in a folder (path=C:\Users\WD\) in my local desktop.
Each workbook has two sheets (Section-A and Section-B).

Each sheet contains employee data scattered in different cells.

Sheet - Section-A contains the below:
Employee Name is in D5
Department is in D6
Date of Joining is in D7
Reporting Manager is in D8
Employee Code is in N5
Designation is in N6
Type of Appraisal is in N7
Review Period: is in N8
Primary Skills is in D12
Secondary Skills is in N12
Section A - Goals Achieved % is in a merged cells of J,K,L 27
Section A - Goal Achieved % is in a merged cells of P, Q, R 27

In sheet name - Section-B, the below are present:
Self Appraisals Rating in G31
Manager Rating in L31

I want all these data present in 100 different workbooks in the folder to a master workbook as a listing:
Each item should be put under the headers from all the cells of all the workbooks.


Employee Name Department Date of Joining Reporting Manager Employee Code Designation Type of Appraisal Review Period: Primary Skills Secondary Skills Section A - Goals Achieved % Section A - Goal Achieved % Self Appraisals Rating Manager Rating
Mr. Smith Sales 05-12-2017 Mr. John 1001 Manager Annual 2017
Mr. Snow Marketing 05-12-2017 Mr. John 1002 Trainee Annual 2017
Mr. Eddie Sales 05-12-2017 Mr. John 1003 Trainee Annual 2017



Many thanks in advance.