Hi all,

Have search the groups but didn't find an answer to my problem,
therefore here my question.

Same workbook, two different sheets. Excel 2003 on W2000

source cells: Range of cells are formatted as custom date. Majority of
cells contain valid date, some of them contains text as: no date for
invoice (without any quotes etc.)

target cells: empty and formatted as custom date (same as source cells)

Applying a macro that copies source to date (no formatting) with a
copy/paste (not copy value) sometimes screws up the format of the
target cells transforming them to general, and of-course displaying a
number i.e. of the date.
The strangest thing is that it screw up randomly.

Any ideas ?

Best regards,

Ronald.

wrote in message
oups.com...
Hi all,

Have search the groups but didn't find an answer to my problem,
therefore here my question.

Same workbook, two different sheets. Excel 2003 on W2000

source cells: Range of cells are formatted as custom date. Majority of
cells contain valid date, some of them contains text as: no date for
invoice (without any quotes etc.)

target cells: empty and formatted as custom date (same as source cells)

Applying a macro that copies source to date (no formatting) with a
copy/paste (not copy value) sometimes screws up the format of the
target cells transforming them to general, and of-course displaying a
number i.e. of the date.
The strangest thing is that it screw up randomly.

Any ideas ?

Best regards,

Ronald.


First of all, you said some cells contain something other than valid dates,
and since Excel tries to help by guessing formats sometimes, you can expect
random results if the data is also random.

Next: I'm not clear what your macro is doing when it pastes. Is it the
equivalent of using (via the menu, manually) Paste Special, Values?

Thanks for your help so far!
Is there any way to set the "help by guessing formats" off ?

sorry, I wasn't unclear about the macro.
It only does a copy paste (as done by hand). Problem is not sloely when
doing it trough the macro, manualy also.

Thx in advance !!