How to transpose and link data from one sheet to a separate excel sheet?

Hi,

I have two identical tables in Sheet 1 as given below:

Sr. No. Header 0 Header 1 Header 2 Header 3
Response 1 Parameter 1 Data A
Response 1 Parameter 2
Response 1 Parameter 3

Row with some other data
Row with some other data
Row with some other data
Row with some other data

Sr. No. Header 0 Header 1 Header 2 Header 3
Response 2 Parameter 1 Data B
Response 2 Parameter 2
Response 2 Parameter 3

Row with some other data
Row with some other data
Row with some other data
Row with some other data


The two tables with Headers and parameters always have 4 rows of space between them. I have a huge set of tables in this way and I would like to transpose the data from these tables into a separate sheet while linking the cells.

So, for eg., in Sheet 2, I have the following table:

Header 1 Header 2 Header 3
Sr. No. Parameter 1 Parameter 2 Parameter 3 Parameter 1 Parameter 2 Parameter 3 Parameter 1 Parameter 2 Parameter 3
Response 1 Data A
Response 2 Data B


Is it possible to link the two sheets and ensure that all recurring tables, the values are picked up - Response 3, Response 4, etc. - by writing a macro/function. I need Data A and Data B and so on to show up in the right cells each time.

Not sure if my question is entirely clear. Let me know if I need to clarify on some point.

Thanks for the help!

I have also attached the table in an Excel in case the text is not clear.