copy worksheet to another workbook

[jtaiariol]

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?

 

[Dave Peterson]

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?

 

[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]

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.

 

[Dave Peterson]

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

Edit|links

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.

 

[Chris Lavender]

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

Best rgds
Chris Lav

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.