I have a workbook with a worksheet that referances other worksheets within
the same workbook. When I copy this worksheet to another workbook, it still
referances the old workbook....instead of referancing the new workbook. The
worksheets have the same name. How do I make it referance the new workbook?
--
john

jtaiariol wrote:

I have a workbook with a worksheet that referances other worksheets within
the same workbook. When I copy this worksheet to another workbook, it still
referances the old workbook....instead of referancing the new workbook. The
worksheets have the same name. How do I make it referance the new workbook?
--
john

--

Dave Peterson

I like to do this:

Change the formulas to strings, copy the ranges, paste the ranges, and then
convert them back to formulas.

Select the range to copy in the original workbook
edit|replace
what: = (equal sign)
with: $$$$$
replace all

Then copy|paste. Since you're just pasting strings (not formulas), they won't
point back to the old workbook.

After you paste, do the opposite:

Select the pasted range
edit|replace
what: $$$$$
with: =
replace all

Do it for both ranges.

And don't forget to fix the original workbook (or close it without saving).

jtaiariol wrote:

I have a workbook with a worksheet that referances other worksheets within
the same workbook. When I copy this worksheet to another workbook, it still
referances the old workbook....instead of referancing the new workbook. The
worksheets have the same name. How do I make it referance the new workbook?
--
john

--

Dave Peterson

thanks Dave.....seems a bit bulky.....but here's what i've been
doing.....copying the range to the other workbook, options-show formulas,
replace [old workbook name] with "nothing".....similar to your solution.....I
guess I thought there was an easier way.
--
john


"Dave Peterson" wrote:

I like to do this:

Change the formulas to strings, copy the ranges, paste the ranges, and then
convert them back to formulas.

Select the range to copy in the original workbook
edit|replace
what: = (equal sign)
with: $$$$$
replace all

Then copy|paste. Since you're just pasting strings (not formulas), they won't
point back to the old workbook.

After you paste, do the opposite:

Select the pasted range
edit|replace
what: $$$$$
with: =
replace all

Do it for both ranges.

And don't forget to fix the original workbook (or close it without saving).

jtaiariol wrote:

I have a workbook with a worksheet that referances other worksheets within
the same workbook. When I copy this worksheet to another workbook, it still
referances the old workbook....instead of referancing the new workbook. The
worksheets have the same name. How do I make it referance the new workbook?
--
john

--

Dave Peterson

After you copy and paste, you could just change the link to the new workbook:

Edit|links

jtaiariol wrote:

thanks Dave.....seems a bit bulky.....but here's what i've been
doing.....copying the range to the other workbook, options-show formulas,
replace [old workbook name] with "nothing".....similar to your solution.....I
guess I thought there was an easier way.
--
john

"Dave Peterson" wrote:

I like to do this:

Change the formulas to strings, copy the ranges, paste the ranges, and then
convert them back to formulas.

Select the range to copy in the original workbook
edit|replace
what: = (equal sign)
with: $$$$$
replace all

Then copy|paste. Since you're just pasting strings (not formulas), they won't
point back to the old workbook.

After you paste, do the opposite:

Select the pasted range
edit|replace
what: $$$$$
with: =
replace all

Do it for both ranges.

And don't forget to fix the original workbook (or close it without saving).

jtaiariol wrote:

I have a workbook with a worksheet that referances other worksheets within
the same workbook. When I copy this worksheet to another workbook, it still
referances the old workbook....instead of referancing the new workbook. The
worksheets have the same name. How do I make it referance the new workbook?
--
john

--

Dave Peterson


--

Dave Peterson

Can't you just go into Edit... Links and change the referenced workbook to
the current one?

Best rgds
Chris Lav

"jtaiariol" wrote in message
...
thanks Dave.....seems a bit bulky.....but here's what i've been
doing.....copying the range to the other workbook, options-show formulas,
replace [old workbook name] with "nothing".....similar to your
solution.....I
guess I thought there was an easier way.
--
john


"Dave Peterson" wrote:

I like to do this:

Change the formulas to strings, copy the ranges, paste the ranges, and
then
convert them back to formulas.

Select the range to copy in the original workbook
edit|replace
what: = (equal sign)
with: $$$$$
replace all

Then copy|paste. Since you're just pasting strings (not formulas), they
won't
point back to the old workbook.

After you paste, do the opposite:

Select the pasted range
edit|replace
what: $$$$$
with: =
replace all

Do it for both ranges.

And don't forget to fix the original workbook (or close it without
saving).

jtaiariol wrote:

I have a workbook with a worksheet that referances other worksheets
within
the same workbook. When I copy this worksheet to another workbook, it
still
referances the old workbook....instead of referancing the new
workbook. The
worksheets have the same name. How do I make it referance the new
workbook?
--
john

--

Dave Peterson