Unpivot data with VBA

Hi,

I have just finished running a VBA that allows me to copy a range from a specific worksheet in multiple workbooks (the sheets are named the same in all workbooks) to a master sheet. However, being rather new to VBA, the module that I ran pastes the ranges below the previously copied worksheet range (see attachment for example) and I'm now presented with the issue of unpivoting this data. I was therefore wondering if there was any Sub pivot_data() VBA script that could help me with my issue.

In my pasted range, the first column defines week name. The first row of the following column includes a text saying "Date" with 7 dates following that (each in respective columns) indicating every week day. Now I would like to upivot this data based on the week name defined in column A, so that the script identifies Week 1 as the start and then loops through Week 1+1 until there are no more weeks left (maximum of 52), and thus unpivoting the correct dates with the correct orders of the certain products. If I haven't explained myself sufficiently, please refer to the attached file where I have manually constructed the preferred outcome of the VBA.

Note, the data in the attached file is only an excerpt of the original file. It contains thousands of rows, so would be helpful if this could work.

Thanks,

Anton