Worksheet.copy changes references

[Marco]

Hi everybody,

We've got a problem with a macro in Excel. The macro
copies 2 worksheets to a new document. Worksheet A has
some formulas referencing cells on Worksheet B. First B is
copied to the new workbook, then A. The formulas on the
copied worksheet A are now referencing the original cell
of worksheet B, instead of the copied ones in the same
workbook.

Can anybody tell me how to keep the references within the
same workbook?

Thanks in advance,

Marco.

 

[ole Michelsen]

If the addresses in the copied "worksheet B" are exactly identical to the
new addresses A1 = A1, then you could use local addresses. NewAddress =
Myrange.Address(false,false).
ActiveCell.Formula = NewAddress
You might have to write formulas like = NewAddress1 & "+" & NewAddress2

Ole Michelsen

 

[Dave Peterson]

One option is to copy both worksheets at the same time.

Another is to convert your formulas to text, copy the worksheet, and change the
text formulas back to formulas

Essentially...
edit|replace
what: =
with: $$$$$$=
replace all

copy the sheet(s)

then edit|replace
what: $$$$$$=
with: =
replace all

One more option is to point at your new workbook: Edit|links|change source