Copy and paste a date value in a VBA macro

Question

I wrote a macro to copy and paste multiple cell values ​​from one sheet to another. It was successful in all fields except one containing the date value.

For example, if the cell contains '08 -Jan-14 ', the inserted value is an integer of "41647".

How to ensure that the inserted value received by the new sheet is 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 just insert values, not formats. You need to add another row after insertion

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

Another way

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

Answer 2

replace:

rngFrom.Copy
rngTo.PasteSpecial Paste:=xlValues

with:

rngFrom.Copy rngTo