Copy formulas to another workbook

[Nadine]

Have 2 workbooks, want to copy a section from one to the other, but the cell
reference refers back to the other workbook.
If I was starting from scratch would just copy the sheet, but can't do that,
as workbooks already contain alot of data and charts, etc.

Just want the formula copied but refer to the cells in the new workbook, and
not reference the other workbook.

Workbooks are open in the same session of excel, not seperate sessions
tried paste special options but cells still refer back to the other workbooks,

Is there a way to do this, so that I don't have to recreate the forumals. As
I got to copy this same to 50 other workbooks.

Thanks
Nadine

 

[Luke M]

Paste the formula like you have been doing, then do a find and replace to
remove:
[*]
Which will remove the workbook reference.

 

[JLatham]

For some reason I can't read Luke M's response, so this may be the same
suggestion.

Make a copy of the 2nd workbook to work/test with. Copy the formulas from
the original into the copy and then close the workbooks.

Open the test copy and use Edit --> Links and Change Source to then browse
and point to the very same file you have open in its location on your hard
drive! If it works well with the copy, then do same thing to the original
(or just save the copy over the original 2nd workbook).

 

[Pete_UK]

When you copy those formulae across, they will refer to the other
workbook by adding [filename.xls] to the cell references. With the
cells still selected after copying/pasting, you can use CTRL-H (Find &
Replace) and set:

Find what: [filename.xls]
Replace with: <leave blank>
then click Replace All

Obviously, you will need to use the name of the file that you copied
the formulae from, and you should ensure that you have the same sheets
and sheetnames as in the other file.

Another way is to highlight the cells that you want to copy and then
use CTRL-H to:

Find what: =
Replace with: zz=
then click Replace All

This will change all those formulae to text, so that you can copy them
normally and paste into the second file. Then you can apply those
changes backwards within the new file by CTRL-H, and:

Find what: zz=
Replace with: =
then click Replace All

and obviously you will need to repeat this in the first file to set it
back to how it was.

Hope this helps.

Pete

 

[Nadine]

Thanks Luke the suggestion:
Paste the formula like you have been doing, then do a find and replace to
remove:
[*]

Worked Perfectly.