You can use Edit | Replace (or CTRL-H) after highlighting all cells in
the sheet in the new workbook to remove the references to the old file
directly, i.e.:

Find What: [oldworkbook.xls]
Replace with: leave blank

then click Replace All.

You will need both files to be open, otherwise the reference to
[oldworkbook.xls] will expand to include the full path.

Hope this helps.

Pete

wrote:
Yeah, seems that the editreplace is the only way to do it.
Thanks for the tip.
Ryan

Gord Dibben wrote:
Nope...........forget that one too.

The editreplace seems the best way to go.


Gord

On Wed, 23 Aug 2006 14:08:12 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Another thought.

Right-click on the sheet tab and "move or copy"

Check mark in "create a copy" and pick New Book from the "to book" dialog.


Gord

On 23 Aug 2006 13:10:43 -0700, wrote:

Seems that when I try any type of Cut/Paste or Cut/Paste Special it
still copies the reference.

Thanks,
R

T Kirtley wrote:
I think the easiest solution would be to copy the sheets that are 'linked to'
at the same time as the sheet that they are 'linked from'. In other words,
group all the related sheets in the initial workbook (use Ctrl^Clicks to
select each), and copy/paste them into a new workbook all at once. This way
the formulas in the copied sheets should no longer be linked to the original
workbook, but to the copies that were made.

HTH,

TK

" wrote:

I am creating a copy of a worksheet into a new workbook and all of the
formulas automatically reference the old workbook.

For example:

Formula in Old Workbook is

=R9*L17+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","Trad eCode","F04")

Once I create a copy of the worksheet which contains this formula and
others like it into a new workbook, the formula automatically changes
too

=R9*L17+GETPIVOTDATA("Billing Amount",'[oldworkbook.xls]Labor
Pivot'!$A$6,"StatementDate",DATE(2006,8,20)","Trad eCode","F04")

I just want the same formula to be copied to the new workbook, not pull
from the old workbook.

Please help.
Ryan



Gord Dibben MS Excel MVP