Thank you for your help.
Finally I find the way for the solution
Instead of just simple paste
i use the pasteSpecial(xlPasteAll)
this will make all format and value preserved without lost

"William Benson" wrote:

OK, did you perhaps copying the format first?

"Laguna" wrote in message
...
this does not work if the data inside the cell have different formats
I CAN GET format character by character, but the program may run slower.
anyway, thank you for your help

"William Benson" wrote:

Go ahead and use the value property as you described, then include these
steps:

oBook.Worksheets(name).Range("$E" & temp2).Copy
newBook.Worksheets(name).Range("$E" & temp1).Select
Selection.PasteSpecial Paste:=xlPasteFormats

There may even be a way around selecting the area to paste to, I didn't
think too much about it.


"Laguna" wrote in message
...
I am using excel 2000

i use the following code:

oBook.Worksheets(name).Range("$E" & temp2).Copy
(newBook.Worksheets(name).Range("$E" & temp1))

the range is with thousand of character

when i copy the range to another sheet, part of the data are missing..

although i can use the range1.value = range2.value to preserve the data
but all the formating in the cell will be lost.