Ensuring a cell is copied in text format

[Richard H]

I have a macro that uses the following code to copy the value from
the named range "a" to the named range "b" in another workbook:

a.Formula = a.Value2
a.Copy b

The problem is that the "b" cell gets formatted as Date, even though
it's preformatted as Text and the "a" cell also is formatted as Text.
(The "a" cell contains text like 11/2004, so the value is interpreted
as a date on arrival.)

I need to ensure that the "b" cell is formatted as Text, and that it
doesn't convert the original text to a "date number" like 38292. How
can this be done??

 

[Harald Staff]

Hi

One way:
b.Value = "'" & a.Text

HTH. Best wishes Harald

 

[Richard H]

Worked beautifully! Takk skal du ha, Harald.

One minor problem, though: The "date" cell is formatted as text in
the original workbook, and written like this:

12/2004

On import, the data is converted to a standard date format, like
this:

Dec.04.

The code you supplied ensures that this is converted to text -
'Dec.04. I would preferably retain 12/2004, as originally entered.
Is that possible??

Richard

 

[Harald Staff]

I'm not sure I understood this. When it comes to dates, the original entry
isn't there anymore. Excel converts it to a date immediately. But if mm/yyyy
is what you need, and a contains a real date, then for text
b.Value = "'" & Format$(a.Value, "mm/yyyy")
or, if you want the date to follow
b.Value = a.Value
b.NumberFormat = "mm/yyyy"

A cell can contain a date and display almost whatever. If it's a date then
it shows a real date in the formula bar. If formula bar also says
Dec 04
or
12/2004
then it's text, not a date.

HTH. Best wishes Harald

 

[Richard H]

Harald,

you understood, alright. The only puzzling thing now is that the
imported "date" is converted to

'12.2004

even though your code

b.Value = "'" & Format$(a.Value, "mm/yyyy")

definitely tells Excel to return '12/2004.

I can't figure that one out, but I can live comfortably with it.
Thank you!

Regards,
Richard