CODEWITHSUNDEEP
excelvbadate
Dr. Thomas C. King
Dr. Thomas C. King

Reputation: 1034

In VBA Excel when I copy one cell to another, if it is a date it is not copied, what do I do?

This is probably quite simple but I've googled and can't find an answer. I am also just learning VBA (I have done VB.NET etc. etc.)

If the cell the macro is trying to copy is a date I just get a number copied, e.g. 40352 from 23/06/2010

Here is a code snippet, any help most appreciated, thanks:

Sheet5.Range(Cells(rwStartNumber, currentColumn + 1).Address(False, False)) =
     Sheet5.Range(Cells(rwStartNumber, currentColumn).Address(False, False))

Obviously this is in two loops, but that's not where the problem is.

Thanks!

Upvotes: 2

Views: 3973

Answers (3)

martin
martin

Reputation: 2638

You can copy the property NumberFormat from the source cell, e.g.:

'Following line copies the values...'
ws.Cells(curRow, curCol + 1) = ws.Cells(curRow, curCol)

'And this copies the formats...'
ws.Cells(curRow, curCol + 1).NumberFormat = ws.Cells(curRow, curCol).NumberFormat

(ws is a variable of the type Worksheet, you can assign it e.g. like this:

Dim ws as Worksheet
Set ws = Worksheets("Sheet5")

or you can just use ActiveSheet)

Upvotes: 0

Trefex
Trefex

Reputation: 2320

You can try the following

With Selection
     .PasteSpecial xlPasteValuesAndNumberFormats
End With

Let me know if it works for you,

Kind Regards,

Upvotes: 2

stuartd
stuartd

Reputation: 73253

You see the number because that's how Excel stores dates: you need to change the format of your target cell to be Date and it will display properly.

Upvotes: 1

Related Questions