Macro that can help create pivot tables off multiple spread sheets?

Can anyone help me (if it's possible)

I have 37 spreadsheet in a folder that each contain one table templates. There are 4 columns from A1-D1, and in rows A2-D2, there are populated cells with data. Rows E2, F2, G2, H2, and so on are also populated with data but they all belong to column D1. There are no headers for rows E1, F1, G1, H1, and so on. It's just the way the template is.

I want to create a pivot table off each template where I have cells A1-D1 and beneath their corresponding population. For D1, I want to have all the cells past D2 in that column transposed, listing down. But with no headers the pivot table doesnt work.

Is there a way to figure this out so I don't have to do the manual work? I attached a sample of the template along with my desired end state. Note there is no standard number of populated cells past D2. Each spreadsheet is different.