Transpose rows from multiple sheets into new sheet

Hi all,

I am having a problem which I have been tackling for several days. Any help will be useful.

In the attached workbook, I would like a macro which copies and transposes data between sheets 'Statistics' & 'Master'.

So in sheet 'makecopy', it needs to

1. Transpose data from B2:B12 and paste it in Flatfile in row A.
2. Transpose data from D2:D12 and paste it Flatfile in row A (in the first available coloumn)
3. Transpose data from B13:B42 and paste it in FlatFile in row A (in the first available coloumn)
4. Transpoe data from F2:F42 and paste it in FlatFile in row A (in the first available coloumn)

Then,

1. Transpose data from C2:E12 and paste it in Flatfile in row B.
2. Transpose data from E2:E12 and paste it Flatfile in row B (in the first available coloumn)
3. Transpose data from C13:C42 and paste it in FlatFile in row B (in the first available coloumn)
4. Transpose data from G2:G42 and paste it in Flatfile in row (in the first available coloumn)

You might get a better idea when you look at the workbook. Any help will be massively appreciated!!!

Hello Mumps,

Thanks for reply, sorry my English is not the best.

'Statistics' and 'Master' are the reference points for the loop. The macro should act on all the sheets in between these two sheets.

Please see attached transpose test data v2. I inserted new sheet called "FlatFile(sample output) which shows the final result (and how it should look in 'Flatfile')

I hope this helps. Please tell me if you need more info.

Thank again for your reply and support!

Thank you for the response Alan. I hope my reply to mumps, makes it a bit more clear.

You could try this on a copy of your original also and see if it is anywhere near what you want.

Please Login or Register  to view this content.

Firstly, thanks everyone for your responses. My bad for not being clear. Please see attached 'Transpose Test Data v3.

The idea is that 'Flatfile' should consolidate all the data in the sheets which exists between 'Statistics' and Master'.

I also attached new sheet called 'Flatfile(sample output)' to show the final output which the macro should perform in 'Flatfile'.

The headers in the 'Flatfile(sample output)' are the fields marked in BOLD in sheets 'makecopy' & 'makecopy(2)'. (You may notice, the headers from the turquoise (left) are pasted first, and then it moves into the blue (right) coloured fields)

Then, it should pull out its adjacent values and fill them out below as shown in 'Flatfile(sampleoutput)'. (This action needs to be performed even if there are more sheets in between 'Statistics' & 'Master').


I sincerely appreciated everyone's effort - I apologies for this strange layout which might make things complex. Thank you again!

Hello Mumps1, Alansidman, Jolivanes, thank for your time and effort. Please see my latest reply - it might make things easier. Let me know if you require more info.

Yes you are right!

Originally Posted by jolivanes

It's nice to color code but for people with Color Vision Deficiency it is not so easy.
I assume that the same blocks of data for each sheet should be transferred. Is that right?

Hi Mumps1, thank you for your time and effort! This works but when I run the macro - I got few issues:

1. In Flatfile, 'Is Macro working? 1' header seems to be missing.
2. In Flatfile, cell "AY1" is not 'Name 12'
3. If I leave E2:E12 blank in 'makecopy' sheet - the flatfile is not leaving it blank and instead pulls out 'Yes/No's. So is it please possible to make the macro a bit more dynamic where it might check for the flatfile header against the makecopy sheet and fills it with its respective response (adjacent cell) rather than just copy/paste.

Thank again for your time and effort! Appreciate it so much.

Originally Posted by Mumps1

Try:

Please Login or Register  to view this content.

Hello Mumps1, thanks for your swift response.

Yes, it is possible that C2:C12/C13:C42 to be blank or blanks to appear randomly in the middle. So, any flexibility to accommodate this will be hugely helpful!