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.
Last edited by nique; 07-22-2014 at 09:50 PM.
Try the following macro:
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.
Please Login or Register to view this content.
HTH!
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
Please Login or Register to view this content.
If solved remember to mark Thread as solved
Hi patel45,
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)!Please Login or Register to view this content.
You are right
Please Login or Register to view this content.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks