Pasting values rather than formulae in VBA.

[adam.coles]

Similar to Paste Special>Paste Values, I want to paste the cell value in a macro. The current portion of the macro is as follows:

Set sh3 = Worksheets("Sheet3")
Set dest = sh3.Range("A5").End(xlDown).Offset(1, 0)
sh3.Range("F4").Copy Destination:=dest

Cell "F4" is copied to the bottom of column A. However, it is copying the formula: =TODAY(). I would like this date to be pasted as the actual date i.e. so that it doesn't change.

Any help would be much appreciated.

Thanks

 

[VoG]

Try

sh3.Range("F4").Copy
dest.PasteSpecial (xlValues)

 

[Scott Huish]

Why do you need to copy at all? Why not just assign the date value to the cell?

Sub test()
Dim sh3 As Worksheet
Set sh3 = Worksheets("Sheet3")
sh3.Range("A5").End(xlDown).Offset(1, 0) = Date
End Sub