I want to copy formulas from a range in worksheet A in workbook A to a
range in worksheet B in workbook B.

When i use the clipboard to copy and paste special by formulas, 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-array formulas

thanks a lot, any help is much appreciated.

It sometimes help to do the copy using the fx box at the top of the
worksheet. click on the cell you want to copy. then highlight the formula
in the Fx box and do a copy. then paste the formula in the Fx box in the 2nd
worksheet.

"MCI" wrote:

> I want to copy formulas from a range in worksheet A in workbook A to a
> range in worksheet B in workbook B.
>
> When i use the clipboard to copy and paste special by formulas, 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-array formulas
>
> thanks a lot, any help is much appreciated.
>

One simple method is to use Edit>Replace a couple times.

Edit>Replace

What: =

With: ^^^

Replace all.

Copy then 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 <(E-Mail Removed)> wrote:

>I want to copy formulas from a range in worksheet A in workbook A to a
>range in worksheet B in workbook B.
>
>When i use the clipboard to copy and paste special by formulas, 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-array formulas
>
>thanks a lot, any help is much appreciated.

thanks a lot, but this doesn't seem to work for array formulas.
for example, in source workbook, range("D910") 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 Edit>Replace a couple times.
>
> Edit>Replace
>
> 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 <330...@gmail.com> 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.

Great tip, works a treat and saved me a pile of work when I'd mucked up the
formulas on a workbook that I'd used as the template for 15 others.

"Gord Dibben" wrote:

> One simple method is to use Edit>Replace a couple times.
>
> Edit>Replace
>
> What: =
>
> With: ^^^
>
> Replace all.
>
> Copy then 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 <(E-Mail Removed)> wrote:
>
> >I want to copy formulas from a range in worksheet A in workbook A to a
> >range in worksheet B in workbook B.
> >
> >When i use the clipboard to copy and paste special by formulas, 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-array formulas
> >
> >thanks a lot, any help is much appreciated.
>
>