VBA Copy Formulas then Paste Value

[gheyman]

I have code that copies a formula down a range. I want to past the Values over the formulas once they are calculated.

Do I have to recopy the paste area and then do a Paste Value? or can this be done in one step?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False

    Dim LastRow As Long
'
    LastRow = ActiveWorkbook.ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
'
    Range("J8").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC3<>"""",R[-3]C3<>"""",RC8<>""""),EOMONTH(R5C3,RC8),"""")"
    Range("K8").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC3<>"""",R5C3<>"""",RC9<>"""",RC10<>""""),EOMONTH(RC10,RC9),"""")"
'
    Range("J8:K8").Select
    Selection.Copy
'
        With ActiveWorkbook.ActiveSheet
            .Range("J8:K" & LastRow).PasteSpecial Paste:=xlPasteFormulas
            End With
'
    Application.CutCopyMode = False
    Range("H8").Select
'
End Sub

 

[Fluff]

Untested, but try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False

    Dim LastRow As Long
'
    LastRow = ActiveWorkbook.ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
'
    Range("J8:J" & LastRow).FormulaR1C1 = _
        "=IF(AND(RC3<>"""",R[-3]C3<>"""",RC8<>""""),EOMONTH(R5C3,RC8),"""")"
    Range("K8:K" & LastRow).FormulaR1C1 = _
        "=IF(AND(RC3<>"""",R5C3<>"""",RC9<>"""",RC10<>""""),EOMONTH(RC10,RC9),"""")"
    Range("J8:K" & LastRow).Value = Range("J8:K" & LastRow).Value
    Range("H8").Select
'
End Sub