Excel 2003: copied date is pasting as a zero

[AngelB]

Hi All

I need to copy some data from one sheet to another using vba. Two of the fields I need to copy are in date format (dd/mm/yyyy). I am using the following syntax to paste them into another workbook:

Range("H2").Copy Destination:=Workbooks("GroupStats.xls").Sheets("Sheet1").Range("B" & j)

j is a counter in a loop as I am looping through a list of values to determine which worksheets I need to copy data from.

The problem I have encountered is that when the data is pasted into the destination worksheet, a zero appears where I would expect to see a date. The format of the destination cells is General as I am performing a Range.Delete before copying and pasting. Is there something obviously wrong with my syntax? I have used this method of copying and pasting before, but not between workbooks. Is there something else I need to do?

Many thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[macropod]

Hi Geraint,

Perhaps if you turn the formula around and reference the values, like:

Workbooks("GroupStats.xls").Sheets("Sheet1").Range("B" & j).Value = Range("H2").Value

If the target id formatted appropriately, the result will appear as a date.

Cheers

[MS MVP - Word]

 

[AnotherHiggins]

What you have looks OK to me. I just set up a dummy WB named GroupStats just to be sure, and it worked fine just as you have it written.

When you click on the destination cell (after running the code), what does it display?

If you insert the following before the line of code you posted, what does it return?

MsgBox Range("H2")

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AngelB]

Thank you both for your replies.

It would appear that I have embarrassed myself, since the pasting was occurring correctly and I was overwriting it later on in the code by incorrectly referencing the cell again and pasting the contents of a blank cell into it. (Pasting C2 into B2 when I meant to paste B2 into C2)

Thank you anyway!


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy