Speed Up Copy & Paste to New Row Macro

Hi All,,

I have thrown together a basic looped 'copy & paste values to new row' module based on online research. The below script takes approx. 35 seconds to complete and this time, I assume, is mainly due to each time CZ27 + 0.01 occurs within the loop 1000's of cell formulas recalculate. But I assume that using copy and pastespecial functions reduce the speed further and was hoping someone could please streamline this. Thank you in advance


Option Explicit

Sub Backtest_Improved()

Dim StartTime As Double
Dim SecondsElapsed As Double

Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim i

Set copySheet = Worksheets("Model - BBW+MFI")
Set pasteSheet = Worksheets("VBA Report")

StartTime = Timer

'Reset all relevant variables back to default

Sheets("Model - BBW+MFI").Range("CZ20").Value = "2"
Sheets("Model - BBW+MFI").Range("CQ5").Value = "2%"
Sheets("Model - BBW+MFI").Range("CZ27").Value = "0.05"

'Loop BBW increase by 0.01

For i = 0 To 7

copySheet.Range("EM3:FU3").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Sheets("Model - BBW+MFI").Range("CZ27") = Sheets("Model - BBW+MFI").Range("CZ27") + 0.01

'Debug.Print i

Next i

Sheets("Model - BBW+MFI").Range("CZ27") = Sheets("Model - BBW+MFI").Range("CZ27") - 0.01

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub