Can this VBA run faster?

[phil133]

Hi. I have written the following VBA

Sub calculate_macro1()'
' test Macro
'


'
    Sheets("P_L 2,ATM").Select
    Range("G6:G400").Select
    Range("G6:G400").Value = 100
    For i = 6 To 500 Step 2
    Range("X" & i).Copy
    Range("G" & i).PasteSpecial (xlPasteValues)
    Range("G" & i).Copy
    Range("G" & i + 1).PasteSpecial (xlPasteValues)
    Range("X" & i).Activate
    ActiveSheet.Calculate
    Next i
End Sub

How can I change it so that it runs faster?

Thanks for any help!

 

[MARK858]

Is it normal that it takes 5 minutes 10 seconds?

Seems very high even for the code you posted as the code below which is yours with a couple of adaptions running on a sheet with only constants ran in 360.51 milliseconds on my laptop.

Sub calculate_macro1()
Dim i As Long
    Application.ScreenUpdating = False
    With Sheets("P_L 2,ATM")
        .Range("G4:G5000").Value = .Range("G2").Value
        For i = 6 To 4000 Step 2
            .Range("G" & i).Resize(2).Value = .Range("AA" & i).Value
        Next i
    End With
    ActiveSheet.Calculate
    Application.ScreenUpdating = True
End Sub

 

[phil133]

Thanks for your help! Unfortunately it took 286 seconds. I would be very grateful if you could take a look at my workbook in this link https://www.dropbox.com/s/fbubvdxb0big1fz/BRC P_L ROV add.xlsm?dl=0 If you run the macro CalculateRuntime_seconds, the calculatemacro1 macro will take 286 seconds!

 

[MARK858]

With calculation set to manual it ran in 2.43 seconds on my laptop, ran on its own.

Sub calculate_macro1xxx()
    Dim i As Long
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    With Sheets("P_L 2,ATM")
        .Range("G4:G5000").Value = .Range("G2").Value
        For i = 6 To 4000 Step 2
            .Range("G" & i).Resize(2).Value = .Range("AA" & i).Value
        Next i
    End With
    
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    ActiveSheet.Calculate
End Sub

 

[phil133]

Thank you! This took it to 6 seconds. I'm very grateful to you!

 

[MARK858]

You are welcome

 

[phil133]

Unfortunately, the results are not how I wanted them. It needs to automatically calculate because ie. G6 is determined by AA6 which is determined by G4. I guess I will stick with the 5 min macro! Better safe than sorry!