Copy formulas between workbooks without copying links
thanks a lot, but this doesn't seem to work for array formulas.
for example, in source workbook, range("D9:D10") has an array formula
of "=A1:A2",
using your way described below, in destination workbook, we would
have:
cell D9 = "=A1:A2"
cell D10 = "=A1:A2"
which gives "#VALUE!" errors and is different from the source
workbook.
On May 4, 1:39 pm, Gord Dibben <gorddibbATshawDOTca wrote:
One simple method is to use EditReplace a couple times.
EditReplace
What: =
With: ^^^
Replace all.
Copythen reverse the process on the target book.
Close the source book without saving or reverse there also.
Gord Dibben MS Excel MVP
On Sat, 3 May 2008 19:53:29 -0700 (PDT), MCI wrote:
I want tocopyformulasfrom a range in worksheet A in workbook A to a
range in worksheet B in workbook B.
When i use the clipboard tocopyand paste special byformulas, I
often create unwanted external links in workbook B (eg. some names in
workbook B could refer to the ranges in workbook A now).
While i could possible fix this later by looking for the external
links and delete them, I'm looking for a simple way to avoid it.
One possible way is do it in VBA:
workbooks(2).sheets(1).range("A1:A10").formula =
workbooks(1).sheets(1).range("A1:A10").formula
but looks like this only works for non-arrayformulas
thanks a lot, any help is much appreciated.
|