Trying to replace cell formula with just its value after the calculation is complete

Hi, I am trying to create a function that I can use on any cell in my workbook. I want it to essentially copy the cell and paste special the value back into the same cell. I am trying to do so with the following code but its not working:

Function PasteValue()

Range(ActiveCell).Copy
Range(ActiveCell).PasteSpecial xlPasteValues

End Function

Any help would be appreciated, thanks!

Hi,

How about this?

ActiveCell.Value = ActiveCell.Value

OK, that's a little more difficult. A customer function (UDF) works the same as any other function - it will stay present in the cell until you write over it as we've discussed above.

If you need just values in specific cells you could maybe use a worksheet_change event to drive a macro to do this for you. Are there specific cells that need this treatment?

Sub Maybe

Dim c As Range
    For Each c In ActiveSheet.Range("A1:I50").SpecialCells(xlCellTypeFormulas)    '<---- Change range as required.
        c.Value = c.Value
    Next c
End Sub

Or you can use Union for non adjacent cells

Change cell references as required.

Sub With_Union()
    With Application.Union(Range("C5"), Range("D6"), Range("E7"))
        .Value = .Value
    End With
End Sub