CODEWITHSUNDEEP
vbaexcel
Josh Swanston
Josh Swanston

Reputation: 67

Copying & Pasting a Date Value in a VBA Macro

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

Upvotes: 4

Views: 16922

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149325

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"

Upvotes: 6

Gary's Student
Gary's Student

Reputation: 96781

replace:

rngFrom.Copy
rngTo.PasteSpecial Paste:=xlValues

with:

rngFrom.Copy rngTo

Upvotes: 2

Related Questions