Combining scattered data in multiple workbooks into one worksheet in an organised way

Hello friends,

I get attendance data on a daily basis in multiple files. At the end of the month, I have a collection of 30 - 31 files that contains daily attendance data. I wanted to combine all of them into one worksheet so that I can draw a pivot. I did that using a vba code but the issue is that the data is scattered because of which it makes pivot difficult. Moreover there are merged cells which makes it even more difficult. Can anyone help me with how to get the scattered data aligned in an organized and tabular way? I wanted the macro to read the first file, get all scattered data, convert it in an organized way, append it into the master sheet close the first file then move on to second file and do it till all the workbooks are finished processing. I can only do half the thing which is creating path, reading data from another file and merging them into one sheet but I am clueless how to get the data organized. Kindly see the attached attached two sample sheets that contains data and the master sheet how I want the data to be. There are only 2 files attached, in the real scenario, the files can be more than 2.

Appreciate some help here. Thanks !

I get these files as scheduled reports as attachments in an email. I do not have access to get the reports exported manually for date range and hence vba is my only way out.

Request for some help.
Thanks