vba - 在 VBA 宏中复制和粘贴日期值

Question

我编写了一个宏，用于将多个单元格值从一个工作表复制并粘贴到另一个工作表。除包含日期值的字段外，此操作在所有字段上均成功。

例如，当单元格包含“08-Jan-14”时，粘贴的值为整数“41647”。

如何确保新工作表收到的粘贴值采用日期格式？

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

您只是粘贴值而不是格式。粘贴后需要再添加一行

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

另一种方式

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