Hardcopy list generated by Excel's Data Table, refresh table and repeat

Hi everyone!

I'm wondering if its do able to write a Macro that hardcopies content from Excel's data table function.

Situation
I've created a little simulation tool which has one input cell. There is some randomness involved and every time the result cell's value is slightly different. So I used Excel's data table function to provide me with 300 results in one column. Every time I press F9 the table refreshes with 300 new results. However, the distribution of the 300 results stays roughly the same so I'm happy with the reliability of the results based on one value for the input cell.

The problem
I now wish to generate such random lists for a large number of different values for the input cell. Manually this would be quite a lot of work and I suspect I'll have to repeat this little experiment more often in the future. So it would help me a great deal if a Macro could copy the values of the data table (in say column A), hardcopy it in a different column (e.g. B), change the value of the input cell and then make a new hardcopy of the results (e.g. column C) with the new input value. And repeat.

It would be nice if I could designate the number of hardcopies it should make and the increase in the input cell's value for each ''run''.

I've been trying to make a macro myself but without succes. It could be due to the fact that Excel needs to be able to refresh the sheet between hardcopies. Anyone willing to give it a shot?

Thanks in advance!

Best,
Ronnet

I'm not allowed to attach the actual simulation, its sensitive information.

However, I've attached a demonstration file.

On the first sheet you see the green cell. This is the input value of the simulation. It generates a random result. I've called this the blackbox since I cant attach the actual simulation. To simulate randomness I've used the rand() function in this demonstration file.

On the second sheet I have a data table that basically does the simulation 300 times in one column (the yellow cells) for the input value in the green cell.

Sheet 3 provides an example of what I hope to get from a Macro. Basically hard copies of the yellow cells column with the corresponding green cell on top (for easy reading).

On sheet 1 I've also added 'number of runs' and 'increase per run' this is to control the size of the macro work. It corresponds with the number of hard copied columns on sheet 3 as well as the increase in input cell value seen in the green cell copies on sheeet 3.

Hopefully this helps

Hello Leith Ross, thank you very much. In the example file it works great and exactly as I wanted it to. So thank you very much. Before Monday I'll try to integrate the macro in the simulation. If so, I'll close the thread. Once again, thank you very much!

I've been extensively testing the model and I've run into a small issue. The number of the run indicated in the green top row does not match the results in the yellow rows below. This becomes apparant when you remove variability from the results.

For example: in the output sheet the green input value cell says 304 with a corresponding output value in the yellow cells of 42,74. However, 42,74 should be the output of the input value 303. So essentially the green input alues belong to the yellow outcome values one column to the right. Is it possible to easily fix this?