How do I store the values of a changing cell into an array and then paste it to another sheet?

[GreyHat]

Hi,

I know somebody asked this somehow but I'm relatively new to this and need help.

I'm trying to write a code to accomplish the following but am having massive issues.

I have 2 sheets, "Sheet 1" and "Sheet 2". Theres actually a bunch more sheets in this model but these are important for the macro.

Sheet 1 has a Range, Range B32. B32 is basically a formula/value that constantly changes based on inputs from a monte carlo model I made. Basically every time I recalculate the model, the value is going to change.

Now I want to do 1000 (or maybe n) calculations and then take those different values generated and paste them into "Sheet 2" say Range("B2:B1001") or whatever range. The thing is I don't want to paste the same value everytime or press F2 everytime obviously and then repaste.

I know there has to be a way where I can write a loop to have Excel loop through the calculation n times and store the data into an array without wiping out the old values and then transpose that data into the next sheet.

I feel like I'm coming close but still can't fully figure out wat I'm missing or doing wrong. Everytime I run this macro, the same value gets pasted N number of times and the calculation takes forever depending on the number of iterations.

Someone please help, I know this is simple but I'm new and dumb.

Sub Simulation()




Dim X As Double
Dim MyArray() As Double
ReDim Preserve MyArray(1000)




Application.ScreenUpdating = False


'This will set all the formulas to the simulated value


Worksheets("Sheet1)").Activate
X = Worksheets("Sheet1").Range("B32").Value



For i = 1 To 1000
MyArray(i) = X
Calculate
Next i

Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range("B2:B1001").Resize(1000, 1) = Application.Transpose(MyArray)


End Sub