software

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:
gt;
gt; I have a workbook with a worksheet that referances other worksheets within
gt; the same workbook. When I copy this worksheet to another workbook, it still
gt; referances the old workbook....instead of referancing the new workbook. The
gt; worksheets have the same name. How do I make it referance the new workbook?
gt; --
gt; 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:
gt;
gt; I have a workbook with a worksheet that referances other worksheets within
gt; the same workbook. When I copy this worksheet to another workbook, it still
gt; referances the old workbook....instead of referancing the new workbook. The
gt; worksheets have the same name. How do I make it referance the new workbook?
gt; --
gt; 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 quot;nothingquot;.....similar to your solution.....I
guess I thought there was an easier way.
--
johnquot;Dave Petersonquot; wrote:

gt; I like to do this:
gt;
gt; Change the formulas to strings, copy the ranges, paste the ranges, and then
gt; convert them back to formulas.
gt;
gt; Select the range to copy in the original workbook
gt; edit|replace
gt; what: = (equal sign)
gt; with: $$$$$
gt; replace all
gt;
gt; Then copy|paste. Since you're just pasting strings (not formulas), they won't
gt; point back to the old workbook.
gt;
gt; After you paste, do the opposite:
gt;
gt; Select the pasted range
gt; edit|replace
gt; what: $$$$$
gt; with: =
gt; replace all
gt;
gt; Do it for both ranges.
gt;
gt; And don't forget to fix the original workbook (or close it without saving).
gt;
gt; jtaiariol wrote:
gt; gt;
gt; gt; I have a workbook with a worksheet that referances other worksheets within
gt; gt; the same workbook. When I copy this worksheet to another workbook, it still
gt; gt; referances the old workbook....instead of referancing the new workbook. The
gt; gt; worksheets have the same name. How do I make it referance the new workbook?
gt; gt; --
gt; gt; john
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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

Edit|links

jtaiariol wrote:
gt;
gt; thanks Dave.....seems a bit bulky.....but here's what i've been
gt; doing.....copying the range to the other workbook, options-show formulas,
gt; replace [old workbook name] with quot;nothingquot;.....similar to your solution.....I
gt; guess I thought there was an easier way.
gt; --
gt; john
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; I like to do this:
gt; gt;
gt; gt; Change the formulas to strings, copy the ranges, paste the ranges, and then
gt; gt; convert them back to formulas.
gt; gt;
gt; gt; Select the range to copy in the original workbook
gt; gt; edit|replace
gt; gt; what: = (equal sign)
gt; gt; with: $$$$$
gt; gt; replace all
gt; gt;
gt; gt; Then copy|paste. Since you're just pasting strings (not formulas), they won't
gt; gt; point back to the old workbook.
gt; gt;
gt; gt; After you paste, do the opposite:
gt; gt;
gt; gt; Select the pasted range
gt; gt; edit|replace
gt; gt; what: $$$$$
gt; gt; with: =
gt; gt; replace all
gt; gt;
gt; gt; Do it for both ranges.
gt; gt;
gt; gt; And don't forget to fix the original workbook (or close it without saving).
gt; gt;
gt; gt; jtaiariol wrote:
gt; gt; gt;
gt; gt; gt; I have a workbook with a worksheet that referances other worksheets within
gt; gt; gt; the same workbook. When I copy this worksheet to another workbook, it still
gt; gt; gt; referances the old workbook....instead of referancing the new workbook. The
gt; gt; gt; worksheets have the same name. How do I make it referance the new workbook?
gt; gt; gt; --
gt; gt; gt; john
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

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

Best rgds
Chris Lav

quot;jtaiariolquot; gt; wrote in message
...
gt; thanks Dave.....seems a bit bulky.....but here's what i've been
gt; doing.....copying the range to the other workbook, options-show formulas,
gt; replace [old workbook name] with quot;nothingquot;.....similar to your
solution.....I
gt; guess I thought there was an easier way.
gt; --
gt; john
gt;
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; I like to do this:
gt; gt;
gt; gt; Change the formulas to strings, copy the ranges, paste the ranges, and
then
gt; gt; convert them back to formulas.
gt; gt;
gt; gt; Select the range to copy in the original workbook
gt; gt; edit|replace
gt; gt; what: = (equal sign)
gt; gt; with: $$$$$
gt; gt; replace all
gt; gt;
gt; gt; Then copy|paste. Since you're just pasting strings (not formulas), they
won't
gt; gt; point back to the old workbook.
gt; gt;
gt; gt; After you paste, do the opposite:
gt; gt;
gt; gt; Select the pasted range
gt; gt; edit|replace
gt; gt; what: $$$$$
gt; gt; with: =
gt; gt; replace all
gt; gt;
gt; gt; Do it for both ranges.
gt; gt;
gt; gt; And don't forget to fix the original workbook (or close it without
saving).
gt; gt;
gt; gt; jtaiariol wrote:
gt; gt; gt;
gt; gt; gt; I have a workbook with a worksheet that referances other worksheets
within
gt; gt; gt; the same workbook. When I copy this worksheet to another workbook, it
still
gt; gt; gt; referances the old workbook....instead of referancing the new
workbook. The
gt; gt; gt; worksheets have the same name. How do I make it referance the new
workbook?
gt; gt; gt; --
gt; gt; gt; john
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;