Date FORMAT problem

[Sabo, Eric]

I am trying to copy data from one worksheet to another worksheet, I am using
the following code:


Range("sheetname1!A1").value = range("sheetname2!A1).value

But everytime it is putting the data in as a date going from one worksheet
to the other worksheet, I want the data to be text only and not convert it
into a date.

Is there any easier way to copy a range of cells from one worksheet to
another worksheet?

The hardiest problem I am having is finding the syntax I need to use.

Thanks!

 

[Phaedrus]

Eric,

try this instead
Range("sheetname1!A1").value =
Application.worksheetfunction.Text(range("sheetname2!A1).value,"mm/dd/yyyy")

you can change the 'format' string to macth your need.

Chill!
Phaedrus

 

[Leith Ross]

Hello Eric,

Try using this syntax...

Worksheets("Sheet1").Range("A1").Value =
Worksheets("Sheet2").Range("A1").Value

Substitute your worksheet names for Sheet1 and Sheet2 if the are
different. Also make sure the cell format on the second sheet is the
same as the first sheet.

Sincerely,
Leith Ross

 

[Sabo, Eric]

I tried this, I have numbers like this 11-10-05, when it copies the data
from worksheet1 to worksheet2, when it puts the data in worksheet two it
shows up as date formatted, I don't want this to happen. I want the data
to be the same in both worksheets.

 

[Dave Peterson]

Maybe just changing the format of the cells to match would work:

with worksheets("sheet2").range("a1")
.numberformat = worksheets("Sheet1").range("a1").numberformat
.value = worksheets("sheet1").range("a1").value
end with

or if you really don't want it to be a date:

with worksheets("sheet2").range("a1")
.numberformat = "@" 'text
.value = worksheets("sheet1").range("a1").text
end with

 

[Sabo, Eric]

Dave,
That still didn't work, it still places the values in date format.

 

[Dave Peterson]

What do you want to see?

if you want the serial number for that date, ...

with worksheets("sheet2").range("a1")
.numberformat = "General"
.value = worksheets("sheet1").range("a1").value2
end with

 

[Phaedrus]

Finally, I made it work...

use this
Range("sheetname1!A1").value = "'" & range("sheetname2!A1).value

i.e. a single quote between two double-quote marks prefixed to the
value

this will surely work...
Chill!
Phaedrus

 

[Sabo, Eric]

Thank you very much!

That worked!