CODEWITHSUNDEEP
excelvbadatecopypaste
user2918241
user2918241

Reputation: 31

Microsoft Excel Copy and Paste with Format of Date

Can anyone help me with copying and pasting but keep the format of the data to be date for all the dates and numbers for all the numbers. So for example I have Customer ID which should be 3 digits (000), and date to be in date format. But when I copy and paste, the date became an weird integer and the ID become 1 digit. Here is the output now

Copy from:

001 AVC BDE 130 6/23/2013

When I paste it becomes:

Cust ID Name Type Amount Date 1 AVC BDE 130 41448

Can anyone help? Here is my code

 ActiveSheet.Range("A1:F1").Copy
wkbDisplayOrder.Worksheets("Customer's Order").Select
ActiveSheet.Range("A1:F1").PasteSpecial Paste:=xlPasteValues

Upvotes: 3

Views: 36520

Answers (2)

Automate This
Automate This

Reputation: 31364

Assuming your happy with the formatting that is in the copy fields then you can just do a simple paste instead of paste special. Right now your code is only pasting values because of this line:

Paste:=xlPasteValues

You can also just change it to this instead:

Paste:=xlPasteValuesAndNumberFormats

Upvotes: 5

Santosh
Santosh

Reputation: 12353

Try this code (UN - Tested)

   ActiveSheet.Range("A1:F1").Copy
    wkbDisplayOrder.Worksheets("Customer's Order").Select
    ActiveSheet.Range("A1").NumberFormat = "@"
    ActiveSheet.Range("E1").NumberFormat = "MM/dd/yyyy"
    ActiveSheet.Range("A1:F1").PasteSpecial Paste:=xlPasteValues

Upvotes: 1

Related Questions