PDA

View Full Version : Solved: copy range with formula unchanged



Jackyan
10-03-2008, 08:21 AM
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

jfournier
10-03-2008, 10:16 AM
You may want to try doing:

source.copy dest
dest.formula = source.formula

Jackyan
10-03-2008, 02:37 PM
Got it, thank you very much. The code I use like this:



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