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

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

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