VBA for montecarlo iteration

[MOXY1975]

I am trying to predict a monthly weather dependent variable [X] using a montecarlo simulation.

I have compiled a daily temperature dataset going back 10 years from which I have defined the mean, mode, median, max and min as well as standard deviations and distribution percentiles for each month temperature ranges.

I am using this data to drive a regression model which takes historical X's (of which I have 144) and regresses (using the "Trend" function) against 31 days (or 30 or 28 depending on the month) of randomly created set of temperatures (created using randbetween function where the low and high are driven by subtracting / adding 2 x the standard deviation of the Max or Min temp within a given calendar month). The resulting number is called Output. To iterate this number I need to press F9 and then copy / paste the output.

What I want to do is for each calendar month (1 - 12) [Month is a defined variable], iterate 1,000 times and copy / paste the results to a separate worksheet [called Outputs Range E$12 - P$1000] rather than recording a copy paste macro to refresh then copy and paste 1,000 times per month so 12,000 time in total.

Can anyone help?

Thanks in advance

 

[MOXY1975]

Trying myself without success: can someone help.

Sub Recorded()
'
' Recorded Macro
'
'Dim destsheet As Worksheet
'Dim x As Integer (x = Month)
'Dim i As Long (i = iteration)

'Set destsheet = Sheets("Outputs")

'For i = 1 To 12
'For Iteration = 1 to 1000

'Now what? I think I need to define my output array [e121011] and then I can reference column 1 for January and column 2 for feb etc and then loop for i 1 to 1,000?

'Recorded Macro looks like this: want to loop for 1,000 iterations and paste in destination sheet cell references E12:P1011

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "1"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("E12").Select 'note this is not working?
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "2"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("F12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "3"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("G12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "4"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("H12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "5"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("I12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "6"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("J12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "7"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("K12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "8"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("L12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "9"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("M12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "10"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("N12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "11"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("O12").Select
Selection.PasteSpecial Paste:=xlPasteValues

Application.Goto Reference:="Month"
ActiveCell.FormulaR1C1 = "12"
Application.Goto Reference:="Power_Output"
Selection.Copy Destination:=Sheets("Outputs").Range("P12").Select
Selection.PasteSpecial Paste:=xlPasteValues

End Sub

 

[MOXY1975]

Here's my solution: Sub MonteCarloPower()

Application.ScreenUpdating = False

Dim m As Integer
Dim i As Long

Power = Power_Output

For m = 1 To 12

Range("Month").Value = m

For i = 1 To 1000

Application.Goto Reference:="Power_Output"
ActiveCell.Copy
Application.Goto Reference:="Outputs"
ActiveCell.Offset(i - 1, m - 1).PasteSpecial xlPasteValues

Next i

Next m

Application.ScreenUpdating = True

End Sub