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

Hi Nadine,

Yes, there is a way to copy formulas from one workbook to another and have them reference the cells in the new workbook instead of the original workbook. Here's how you can do it:

1. Open both workbooks in the same Excel session.
2. In the workbook that contains the formulas you want to copy, select the cells that contain the formulas.
3. Press Ctrl+C to copy the cells.
4. Switch to the other workbook where you want to paste the formulas.
5. Select the cell where you want to paste the formulas.
6. Right-click the cell and select "Paste Special" from the context menu.
7. In the "Paste Special" dialog box, select "Formulas" from the list of options.
8. Check the box next to "Values" to paste the values of the formulas instead of the formulas themselves.
9. Check the box next to "Skip blanks" if you want to skip any blank cells in the selection.
10. Click "OK" to paste the formulas into the new workbook.

The formulas should now reference the cells in the new workbook instead of the original workbook. You can repeat these steps for the other 50 workbooks you need to copy the formulas to.

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

Luke M
*Remember to click "yes" if this post helped you!*


"Nadine" wrote:

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

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).

"Nadine" wrote:

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

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

On Feb 11, 7:17*pm, Nadine wrote:
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

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

Worked Perfectly.