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

One method which always works is.........

Before copying to new workbook do an EditReplace

what: =

with: ^^^^

Replace all.

Copy to the new workbook and reverse the replace process.

Close source workbook without saving or reverse the replace there also.

I find just closing without save is easiest for me.


Gord Dibben MS Excel MVP

On 23 Aug 2006 11:29:53 -0700, 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)","Tra deCode","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)","Tra deCode","F04")

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

Please help.
Ryan

You have links. I use Copy Paste Special Formulas.

" 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,

Great idea, I was overthinking it. Some of the formulas are over 255
characters so they wont transfer properly, but its better than updating
100 of links.

Thanks,
Ryan

Gord Dibben wrote:
One method which always works is.........

Before copying to new workbook do an EditReplace

what: =

with: ^^^^

Replace all.

Copy to the new workbook and reverse the replace process.

Close source workbook without saving or reverse the replace there also.

I find just closing without save is easiest for me.


Gord Dibben MS Excel MVP

On 23 Aug 2006 11:29:53 -0700, 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)","Tra deCode","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)","Tra deCode","F04")

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

Please help.
Ryan

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

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

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

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

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

Sorry for not replying sooner, but my approach does work; I do it all the time.

To copy the sheets all at once you must select them as a group before
copying them. You can then copy them by dragging the grouped sheets with the
right mouse button pressed into the new workbook. If this is done then the
copied sheets will reference each other, not the source workbook.

TK

" 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

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