Pasting values rather than formulae in VBA.

adam.coles

New Member
Joined
Sep 14, 2006
Messages
3
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why do you need to copy at all? Why not just assign the date value to the cell?

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

Watch MrExcel Video

Forum statistics

Threads
1,113,955
Messages
5,545,162
Members
410,667
Latest member
Gaexel
Top