+ Reply to Thread
Results 1 to 7 of 7

Copy column and transpose to row - Multiple worksheets to summary sheet.

  1. #1
    Registered User
    Join Date
    02-16-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Copy column and transpose to row - Multiple worksheets to summary sheet.

    Hoping someone can save me from a tonne of copying and pasting - and maybe a shred of my sanity.

    I have over 200 worksheets - separate participants data. On each sheet there is a summary column of data at the moment. I now want those columns of data copied to a summary sheet but transposed to rows.

    I have attached an example with 3 worksheets and the sort of summary sheet I am after. Thank you in advance for any help.
    Attached Files Attached Files
    Last edited by nique; 07-22-2014 at 09:50 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Copy column and transpose to row - Multiple worksheets to summary sheet.

    Try the following macro:
    Please Login or Register  to view this content.
    For each row on SummarySheet, the individual worksheet name is in Column A. Column B to CK show the transposed values from the Column L of the worksheet.
    HTH!

  3. #3
    Forum Contributor
    Join Date
    03-11-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    379

    Red face Re: Copy column and transpose to row - Multiple worksheets to summary sheet.

    Hi nique,

    To make an exact replica of the output required as you have shown in SummarySheet
    Use Transpose Function in cell B4:CK4=TRANSPOSE('190'!$L$2:$L$89)

    This is an array formula, use Ctrl + Shift + Enter.

    Please check attached file.

    If any further assistance is required then let me know.

    Regards,
    Ashish Mehra
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Copy column and transpose to row - Multiple worksheets to summary sheet.

    Please Login or Register  to view this content.
    If solved remember to mark Thread as solved

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Copy column and transpose to row - Multiple worksheets to summary sheet.

    Hi patel45,
    Please Login or Register  to view this content.
    Is there a possibility that this exits the "For" loop before all the worksheets are processed. What if "Summarysheet" is not the last sheet in the workbook? (Like, if more sheets are added to the workbook subsequently)!

  6. #6
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Copy column and transpose to row - Multiple worksheets to summary sheet.

    You are right
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    02-16-2011
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Copy column and transpose to row - Multiple worksheets to summary sheet.

    Thank you to both jewelsharma and patel45 - both macros work perfectly.

    I had to make one minor adjustment to paste the values "Paste:=xlPasteValues" as I have formulas to get those summary statistics.

    Other than that, you have both saved my sanity and workload. Thank you

+ 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. Help! Copy totals from multiple worksheets into one summary sheet
    By taryne in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-11-2014, 02:13 AM
  2. macro to copy column A from multiple sheets onto summary sheet
    By fabrecass in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-31-2012, 05:46 PM
  3. Search multiple worksheets and copy cells to summary sheet
    By crttrlvr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-09-2012, 03:01 PM
  4. Macro to copy rows from multiple worksheets to summary sheet
    By zmbd in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2011, 10:21 AM
  5. Replies: 2
    Last Post: 07-23-2009, 05:33 PM

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