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.