How to copy formula to a different workbook so it uses the new workbook's data?

[Holly Balasubramanian]

I would like to copy a formula (actually a worksheet full
of data analysis, which is a LOT of formulas) to another
workbook, and after I copy the formulas, I want them to
operate on the new workbook's data. I can only get the
stuff to copy and reference the old workbook's data and
calculate values for that. Is there a way to paste
without pasting the reference to the workbook name
itself? I do not want to have to change the workbook name
in ALL of the cells.

 

[Pierre Leclerc]

Hi

Use the find/replace in your new workbook to replace the referenc to
the old workbook with nothing

Find other interesting tips at:

http://www.excel-vba.com

I would like to copy a formula (actually a worksheet full
of data analysis, which is a LOT of formulas) to another
workbook, and after I copy the formulas, I want them to
operate on the new workbook's data. I can only get the
stuff to copy and reference the old workbook's data and
calculate values for that. Is there a way to paste
without pasting the reference to the workbook name
itself? I do not want to have to change the workbook name
in ALL of the cells.

Pierre Leclerc
http://www.excel-vba.com
(e-mail address removed)

 

[MS]

Try right click the tabsheet with the formulas in and then copy the
worksheet.

this will be exactly the same with a different name.

 

[felicityiam]

Just copy the sheet from source work book to the new one by right clicking on the sheet name. Once copied, click on any cell in the concerned sheet with obsolete links then, (in excel 2007) go to home>editing>Find & Select then click Replace in the drop down options. Type in the Find What field the obsolete link or file name of source sheet and enclose in [ ] e.g. [previous file.xls] then keep the Replace with field empty. Click Replace All. You'll see the old links erased, thus the formulas will work for the new data in your new workbook. This definitely worked for me .