Using VBA arrays to calculate cumulative average

[qefsadkfpok]

*VBA newbie alert!*

Hi,

Have tried searching but I can't find an answer so thought I would give posting a go.

I have a model in a workbook which carries out a series of calculations on a population across a number of years. The calculations are carried out by formulas and are the same every year.

At the moment I use a simple VBA macro to push the data through from one year to the next (only rows which are eligible are pushed from one year to the next). At the end of the simulation, it works out averages across the period. It runs in a loop so I can do a Monte Carlo type simulation, but at the moment it just spits the results into a summary sheet, copies the summary sheet and then starts the loop again.

The code is something like:

Run X times:

Calculate formulas in year 1; push eligible rows to year 2;
Calculate formulas in year 2; push eligible rows to year 3;
etc.
After year 10, aggregate the results into a summary sheet;
Copy the summary sheet into a new workbook;
Start loop again

What I would like to do is use another macro to store the details of the summary sheet so that a "summary of summaries" can be built across several runs of the model (rather than at the moment where I get X number of summaries and have to aggregate them manually), but I can't figure out where to start. Any help or suggestions would be greatly appreciated!

Thanks