Copying & Pasting a Date Value in a VBA Macro

Question

I have written a macro to copy and paste several cell values from one worksheet to another. This has been successful on all fields except one containing a date value.

For example when the cell contains '08-Jan-14' the pasted value is an integer '41647'.

How do I ensure the pasted value received by the new worksheet will be in date format?

Sub place_data(Source As Worksheet, Destination As Worksheet, Optional WorkbookName As String,  
Optional rowToWrite As Integer)

Dim iSourceHeader As Integer
Dim iCol As Integer
Dim lSourceEnd As Long
Dim lDestEnd As Long
Dim rSource As Range
Dim rDestination As Range
Dim rngFrom As Excel.Range
Dim rngTo As Excel.Range

Set rngFrom = Workbooks(WorkbookName).Sheets(Source.Name).Range("D51")
Set rngTo = ThisWorkbook.Sheets("Self Test Summary").Range("A" & rowToWrite)

rngFrom.Copy
rngTo.PasteSpecial Paste:=xlValues

Answer 1

You are just pasting the values and not the formats. You need to add one more line after pasting

rngFrom.Copy
rngTo.PasteSpecial Paste:=xlValues
rngTo.Numberformat = "DD-MMM-YY"

Another Way

rngTo.Value = rngFrom.Value
rngTo.Numberformat = "DD-MMM-YY"