Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compiling data from multiple Excel sheets - macro?
Hi,
My lab is running a program that produces data for each file on separate work sheets with 54 lines of data per sheet. I now need to compiled all of the data from each sheet into one worksheet, with one row per file (one file=one person in research project). I've heard a macro is the way to do this but have little to no experience with this. My worksheets are piling up and I can't imagine having to copy and paste these every time! Any thoughts would be very much appreciated -- thank you in advance for your time!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compiling data from multiple Excel sheets - macro?
Look at this:
http://www.rondebruin.nl/copy2.htm I've used it multiple times! It works great!! Regards, Ryan--- -- RyGuy "Stephanie Mistretta" wrote: Hi, My lab is running a program that produces data for each file on separate work sheets with 54 lines of data per sheet. I now need to compiled all of the data from each sheet into one worksheet, with one row per file (one file=one person in research project). I've heard a macro is the way to do this but have little to no experience with this. My worksheets are piling up and I can't imagine having to copy and paste these every time! Any thoughts would be very much appreciated -- thank you in advance for your time!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
compiling data from multiple Excel sheets - macro?
.. need to compile all of the data from each sheet into one worksheet
Perhaps you meant to do something like this .. Assume you have identically structured sheets named simply as: 1, 2, 3, ... Assume source data is within A2:B4 in each sheet, eg in sheet: 1, 1 11 2 22 3 33 and in sheet: 2, 11 111 22 222 33 333 In a new sheet, Place this in say, A2: =OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/3)+1&"'!A2"),MOD(ROWS($1:1)-1,3),COLUMNS($A:A)-1) Copy A2 to B2, fill down as far as required. This will extract & stack data from all sheets named: 1, 2, 3 etc in sequence by the sheetname #, viz for the example data above, it'll appear as: 1 11 2 22 3 33 11 111 22 222 33 333 etc Points to note when adapting the above to suit: 1. The top left, anchor cell in the above source sheets: 1,2 is A2, viz this part: ..&"'!A2". Change the "A2" to suit your actual anchor 2. The "3" within both the INT and MOD parts, viz: INT((ROWS($1:1)-1)/3) MOD(ROWS($1:1)-1,3) refer to the number of rows to be extracted in each sheet. Change this number to suit your actuals Example: Suppose your anchor cell is say, A5, and you have 54 lines to extract from each sheet, then you could use this in A2 in the new sheet: =OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/54)+1&"'!A5"),MOD(ROWS($1:1)-1,54),COLUMNS($A:A)-1) Copy across by as many cols as required, fill down as far as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Stephanie Mistretta" wrote: My lab is running a program that produces data for each file on separate work sheets with 54 lines of data per sheet. I now need to compiled all of the data from each sheet into one worksheet, with one row per file (one file=one person in research project). I've heard a macro is the way to do this but have little to no experience with this. My worksheets are piling up and I can't imagine having to copy and paste these every time! Any thoughts would be very much appreciated -- thank you in advance for your time!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compiling data from multiple excel files | Excel Discussion (Misc queries) | |||
Compiling data from many work sheets to one | Excel Discussion (Misc queries) | |||
Compiling data from multiple worksheets into one worksheet | Excel Discussion (Misc queries) | |||
Compiling information from multiple excel files | Excel Discussion (Misc queries) | |||
comparing and compiling between sheets | Excel Discussion (Misc queries) |