+ Reply to Thread
Results 1 to 17 of 17

Transpose rows from multiple sheets into new sheet

  1. #1
    Registered User
    Join Date
    05-14-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    3

    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!!!
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,240

    Re: Transpose rows from multiple sheets into new sheet

    We need some clarification. You refer to sheets 'Statistics' and 'Master' but your examples refer to sheets 'makecopy' and 'Flatfile '. You refer to row A and row B. However, letters are used to name columns not rows. It would help to understand better what you want to do if you could attach a file that shows a "Before" and "After" view of your data.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,779

    Re: Transpose rows from multiple sheets into new sheet

    In addition to Mumps comments, I am not sure what you mean by Transpose. In excel it means to copy a column of data and place it in a row in another location. Is this what you want or are you just asking for it to be copied and pasted in the same manner. Column presentation to column presentation. These are important understandings of what you need to happen. As Mumps suggested a sample Before and After would be very helpful.

  4. #4
    Registered User
    Join Date
    05-14-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    3

    Re: Transpose rows from multiple sheets into new sheet

    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!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-14-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    3

    Re: Transpose rows from multiple sheets into new sheet

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

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,240

    Re: Transpose rows from multiple sheets into new sheet

    In the "FlatFile (sample output)" sheet AQ1:AQ2 and BX1:BX2 are blank. Could you explain why these cells are blank? Cell G12 in the "makeup" sheet contains the text "Agree/Disagree" but this cell is not included in the "FlatFile (sample output)" sheet even though you said "Transpose data from G2:G42 and paste it in Flatfile". Also, the "FlatFile (sample output)" sheet seems to be missing the data from B2:B12, D2:D12, B13:B42 and F2:F42 of the "makecopy (2)" sheet. Please clarify in detail.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    18,779

    Re: Transpose rows from multiple sheets into new sheet

    If I was able to understand your request accurately then the following should work for you. Note however, that in Column C, I had to unmerge cells. VBA does not work with merged cells, so it is important that you Not employ that feature in excel, ever. If you need to show a value across several cells, then use the Center Accross Feature.

    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,359

    Re: Transpose rows from multiple sheets into new sheet

    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.

  9. #9
    Registered User
    Join Date
    05-14-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Re: Transpose rows from multiple sheets into new sheet

    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!
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-14-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Re: Transpose rows from multiple sheets into new sheet

    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.

  11. #11
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    3,359

    Re: Transpose rows from multiple sheets into new sheet

    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?
    Last edited by jolivanes; 05-15-2019 at 12:00 PM.

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,240

    Re: Transpose rows from multiple sheets into new sheet

    Try:
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    05-14-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Re: Transpose rows from multiple sheets into new sheet

    Yes you are right!
    Quote Originally Posted by jolivanes View Post
    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?

  14. #14
    Registered User
    Join Date
    05-14-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Re: Transpose rows from multiple sheets into new sheet

    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.

    Quote Originally Posted by Mumps1 View Post
    Try:
    Please Login or Register  to view this content.

  15. #15
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,240

    Re: Transpose rows from multiple sheets into new sheet

    This revised version will take care of the first 2 problems. Is it possible that any range other than E2:E12 will be blank?
    Please Login or Register  to view this content.
    When replying, please click the "Reply" button instead of the "Reply With Quote" button. This keeps things less cluttered.

  16. #16
    Registered User
    Join Date
    05-14-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    5

    Re: Transpose rows from multiple sheets into new sheet

    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!

  17. #17
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,240

    Re: Transpose rows from multiple sheets into new sheet

    To be honest, I'm not sure how I would go about accommodating the blank cells. Would it work for you if instead of leaving the cells blank, you put a character such as a hyphen (-) in each cell? This would take care of the problem.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Moving Rows from Multiple Sheets to a New Sheet
    By CrimsonHearts in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2018, 01:07 AM
  2. [SOLVED] Transpose specific row of multiple sheets into one single sheet using VBA
    By haer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-09-2016, 11:17 PM
  3. [SOLVED] How to transpose multiple columns and multiple rows in a sheet to another sheet
    By manojuasc in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-28-2015, 05:59 PM
  4. Combineing specific rows from multiple sheets to one sheet
    By Muttly in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 02-14-2013, 10:17 AM
  5. Transpose multiple rows to columns sheet attached
    By ahad_bwp in forum Excel General
    Replies: 4
    Last Post: 07-11-2012, 08:51 AM
  6. Search for Multiple String Values on Multiple Sheets and Copy Rows to New Sheet
    By rrtikker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-15-2012, 12:21 PM
  7. copy rows from 1 sheet to multiple sheets
    By GatorHunter in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-04-2010, 08:26 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1