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

kjen518

New Member
Joined
May 27, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Example Worksheet.png
    Example Worksheet.png
    153.4 KB · Views: 7

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

kjen518

New Member
Joined
May 27, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,159
Messages
5,745,116
Members
423,925
Latest member
globaltlg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top