The easiest way to copy a sheet of formulas! (Excel 2007)

[Robert Crandal]

Hi everyone! After many hours of fiddling with Excel 2007,
I believe that I have finally discovered the easiest way
to copy a full sheet of formulas between workbooks which
eliminates references to the source workbook.

Several people here have suggested solutions which involve the
use VBA code or copying each individual formula from the formula
bar and pasting them into the destination sheet's cells. I believe
that I found an easier way (with Excel 2007)

Suppose you have two workbooks: "formulas.xlsx" and
"dest.xlsx".

1) Go to "formulas.xlsx", find the sheet that contains all your
formulas. Highlight or select the entire range/area that
contains the formulas.

2) Go to the "dest.xlsx" workbook and do a normal paste
operation. (Ctrl-V will do fine)

3) On the "dest.xlsx" workbook, go to the tab/menu titled
"Data". You should see a menu/button option titled
"Edit Links". Press "Edit Links"

4) Press "Change Source". In the file select dialog box,
select "dest.xlsx"

That's it! Your done!

As a side note, the above steps will NOT work if the "formulas.xlsx"
contains sheet names or references that do not exist in the destination
sheet. For example, if one formula is "=Sum(A1:MySheet)", and
"MySheet" does not exist in "dest.xlsx", then the above procedure
will fail.

Hope that helps everyone!

 

[Gord Dibben]

That is one way.

I generally just do an edit>replace in source.

Select the formulas.

Edit>Replace

What: =

With: ^^^

Replace all.

Copy to new workbook.

Reverse the edit>replace.

Close source wb without saving.


Gord Dibben MS Excel MVP