Compile data on multiple sheets.

Hi,

I am using Office 2003. I am not sure if this can be done with a formula,
or if it needs to be programmed in a macro, or even the best way to approach
this. I keep getting confused and frustrated and start over, and over..

I have a template worksheet that I have copied with names of the worksheets
generated automatically from a list. (via macro) I would like to compile the
data from the ranges W7:Y66 (a summary of each worksheet) sequentially onto
another “tally” sheet. In other words, have listed left to right the data
from range W7:Y66 from each sheet next to each other for however many sheets
I happen to have generated.

I have the name of the worksheets listed automatically starting in cell D4
(merged into three cells) and continuing to G4,J4,M4, etc. in my “tally”
sheet upon the generation of the worksheets. I have tried to figure a way to
use that information but I haven’t figured one out. Below the name is where I
would like the information to go, starting D7:F66.

If you have any ideas or questions please let me know.

Thanks,

Jason