Macro for recalculations

[gaw1za]

I have 2 sheets:
"results" and "numbers"

In cells a1 to c1 of the "numbers" sheet I have a random number generator, e.g. rand().

Each time I F9 the cells recalculate.

What I would like is to have for a = 1 To 1000 say, the spreadsheet must recalculate, then copy the contents of a1 to c1 in the "numbers" sheet to the first row of the "results" sheet. A second simulation is performed and then the random numbers are copied to the second row of the "results" sheet and so on.

That is, whatever calculations I perform in the "numbers" sheet will be copied and paste special to the "results" sheet so that at the end of it all I have stored 1000 simulations or results of the calculations I am performing in the "numbers" sheet.

Thanks.

 

[btadams]

You could accomplish it with a macro but before going to that trouble why not fill the Rand functions in A1:C1 down to row 1000, then copy and paste values in the next sheet?

 

[gaw1za]

Hi, I'm just using an example, I'd like to build a more complex spreadsheet and need this as one of the tools, i.e. a method of generating a simulation and recording the results somewhere.

 

[btadams]

This macro could be called from the Worksheet_Calculate event:

Sub GenRandom()
Dim strSht As String, strNewSht As String

    strSht = ActiveSheet.Name
    Sheets.Add after:=Sheets(Sheets.Count)
    strNewSht = ActiveSheet.Name
    Sheets(strSht).Activate
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    For i = 1 To 1000
        Range("A1:C1").Copy
        Sheets(strNewSht).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Calculate
    Next i
    Sheets(strNewSht).Activate
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub