linking same column from multiple sheets and transposing it to separate rows?

[yoyop]

hi
i have a file with hundreds of sheets all are same table with same columns only different data.
what i am trying is to have another sheet that will collect specific data from all sheets, and will contain the information from that one column in all sheets but sorted in rows not one under the other (for every sheet- a row with the column data)

i have trued pivots and power query but couldn't get it right.

i am thinking about using the linking option but u cannot transpose and link at the same time and doing that for each sheet seems wrong

i have no idea in VB unfortunately
is there any good idea?
a free add in may b?

thanks!