VBA Copy and Paste Values in a Worksheet N number of times with calculations

[kjen518]

Hi y'all,

I have a seemingly simple code request. I am have built a Monte Carlo Model that computes an individual Loan Performance based on parameters and random inputs. I have the model built how I want on a separate sheet, and if functions how I would like. Every time I hit "Calculate Workbook" it generates a new output. Great.

Now I am trying on a separate sheet ("Simulation") I have a various inputs for a user to do in rows 1:12. These are static. in Cell D11 is a variable N that is the number of simulations I am looking to run based on the variables. In Row 14 I have column Names for various metrics/outputs referencing cells on the Model Tab. In row 13 I have the Values linked to the Model. I am looking to write a VBA code that calculates the workbook, copies a Trial # into H13 (this is just a number reference), copies the values in Row 13, paste the values in the rows below Row 14 for the N number of times I am loooking to simulate.

Below is the messy/ugly code I have at the moment. It works how I want it to; however, it is VERY slow. It takes like 15+ minutes to run a simulation 1000 times.

Is there a faster more efficient code I can use? Am I being to greedy expecting a faster Run time? Any help would be greatly appreciated. Note some of the variables are not used.


Sub RunSimulationMC1()
Dim CopyTarget As Range
Dim Sht As Worksheet
Dim lRow As Long
Dim pRow As Long
Dim CopyRep As Integer
Dim cpyRow As Integer


' modify Sheet1 to your Sheet name
Set Sht = ThisWorkbook.Sheets("Simulation")
Set CopyTarget = Sht.Range("13:13")

' modify 1 form the line number you are starting your data
pRow = 14

With Sht
nTimes = .Range("D11").Value

For i = 1 To nTimes
Calculate
Sht.Range("H13").Value = i
CopyTarget.Copy
Sht.Range("15:15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Next i

End With

End Sub

 

[kjen518]

Also, note: the above code doesn't exactly work. While it does copy and past the row N number of times, each row is not an independent trial of hte model. I am getting clusters of the exact same output.