Solved: copy range with formula unchanged

Hi,

I've been searching internet for quite a long time without luck, hope somebody can shed some light here.

I have two workbooks, let's say A and B, I need to copy a range R1 in Sheet1 of workbook A to Sheet1 of workbook B. The code I use like this:

Set source = Workbooks("C:\tmp\A.xls").Sheets("Sheet1").Range("A1:A10")
Set dest = Workbooks("C:\tmp\B.xls").Sheets("Sheet1").Range("A1")
source.copy dest

The problem I have is some of the cells in source have reference like this:

=OFFSET(Sheet2!A1,0,1,1,1)

After copy, in Sheet1 of B it becomes:

=OFFSET([A.xls]Sheet2!A1,0,1,1,1)

How can I keep the original OFFSET unchanged?

Thanks in advance!

Jack

You may want to try doing:

source.copy dest
dest.formula = source.formula

Got it, thank you very much. The code I use like this:

Code:

---

Sub copy1()
    Set sbook = Workbooks.Open("C:\tmp\aaa.xls")
    Set dbook = Workbooks.Open("C:\tmp\bbb.xls")
   
    Windows("bbb.xls").Activate
    Sheets("Sheet1").Activate
    Set source = Range("A1:A10")
   
    Windows("aaa.xls").Activate
    Sheets("Sheet1").Activate
    Set dest = Range("A1:A10")
     
    dest.Value = source.Formula
   

End Sub

---