Enhance macro performance

[charm1]

Hi Excel Gurus,

I am novice with VBA. I have created a macro through recording macro and modified it using my little knowledge of VBA, however this macro is taking too much time.

The macro is just pasting a formula in each cell of a range and pasting that cell as values.

Can someone please review this macro enhance it performace.

Sub Macro1()
'
' Macro1 Macro
'

'
Dim Cell As Range
Application.ScreenUpdating = False
With Worksheets("Sheet1")
For Each Cell In .Range("J2:J9586")
    'Range("J2").Select
    Cell.FormulaR1C1 = _
        "MY FORMULA"
    Cell.Copy
    Cell.PasteSpecial xlPasteValues
    Next
End With
    'Range("J3").Select
    'Range("J2").Select
Application.ScreenUpdating = True
End Sub

Thanks,
Charm

[Olly]

You don't need to loop through each cell. Try:

Sub Macro1()
    On Error GoTo Terminate
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    With Worksheets("Sheet1").Range("J2:J9586")
        .FormulaR1C1 = "MY FORMULA"
        .Value = .Value
    End With
    
Terminate:
    If Err Then
        Debug.Print "Error", Err.Number, Err.Description
        Err.Clear
    End If
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

[charm1]

Many many thanks, Olly

This is lightning fast