According to 'tt' worksheet, what is the strategy to detect the data structure / the expected result worksheet layout ?
I'm not sure what you mean?
if you want standard , then you can depend on repeated item(file name) from the first row until to last row for the same item and before header row foe new item(new file name).
As I don't know which way to go, even using only Excel basics the code may have 150 codelines,
it's like john's code.

if you write code based on three ranges as in initial post naturally should not be problem when add new ranges as the structure in file
so I don't need
so in case you have to fit the code yourself
you may need all the summer and maybe the winter to well understand it ‼
bad expression!
it's not necessary to comment like this way

I already see a trap but I suspect another one - guessing time but as guessing can't be coding ! - so
what's trap you're talking about ?
So why not attach such workbook in particular if you are not able to elaborate the strategy ?
first sensitive data
second to make easy the members see a little data instead of follow the whole data , could be bored.
Save the workbook as binary format .xlsb in order to reduce its size, just in the state after such bad data importation …
Then I could better see the traps.
I bet there is no traps , if insist for attaching file with more data I will see what I can do tomorrow.