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

Question

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!

Answer 1

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.

Answer 2

Hi,

You can try the following

With Selection
     .PasteSpecial xlPasteValuesAndNumberFormats
End With

Let me know if it works for you,

Kind Regards,

Answer 3

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)