The data will update so I hope to have a dynamic code. See below. Thanks!

Sheet "Productivity" B6 --> all down until data ends (this is for the dates, which begin in C1 in the destination sheet)
Sheet "Debt" AE10 all down and every column to the right of AE (all down and all to right) until data ends
Sheet "Productivity" C6 all down and every column to the right of AE (all down and all to right) until data ends
Sheet "Terms of Trade" AE10 all down and every column to the right of AE (all down and all to right) until data ends
Sheet "REER" AE10 all down and every column to the right of AE (all down and all to right) until data ends
Sheet "FX" AE10 all down and every column to the right of AE (all down and all to right) until data ends

Essentially, I am shaping my data to upload into Stata in a panel format. On the destinattion sheet Cell A1 is my label for country and B1 is the variable name. C2 is where I transpose the dates. My First country is Australia so A2 to A6 are all Australia. B2 to B6 are debt, productivity, terms of trade REER and FX from each of the worksheets. I need to past every fifrth row since Canada debt is C7.