在 VBA 宏中复制和粘贴日期值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25852538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying & Pasting a Date Value in a VBA Macro
提问by Josh Swanston
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'.
例如，当单元格包含“08-Jan-14”时，粘贴的值为整数“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
回答by Siddharth Rout
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"
回答by Gary's Student
replace:
代替：
rngFrom.Copy
rngTo.PasteSpecial Paste:=xlValues
with:
和：
rngFrom.Copy rngTo
