VBA to transpose data on multiple sheets from single column to rows

Hi Everyone,

I would like to transpose rows of data that are separated by blank lines on multiple sheets onto a single summary worksheet in columns.

There could be multiple sheets so I need it to loop through the multiple worksheets and the dataset includes tabs and in some instances what would be the column row name and it would be helpful if these could be removed

I don't know where to start and would be grateful for any help :-)

Thanks in advance
Bob