Copy tabs(sheets) from workbook without link to original source

[Rich Ulichny]

I want to be able to copy tabs from workbook to workbook. However, when I
right-click..'choose Move or Copy' and copy the tab into a new workbook,
the formula now contains the original source file name.

For example, a simple calc, contained on Sheet2 of my source (book1.xls):

=+Sheet1!A1+Sheet1!A2

becomes

=+[book1.xls]Sheet1!A1+[book.xls]Sheet1!A2

when it's pasted into a new workbook.

I do not want to original link contained in the new workbook. How can I copy
the worksheet without having that orignal source file name in the formula?

Thanks,

Rich

 

[Dave O]

Here's one workaround: before performing the Move/Copy, perform a Find
and Replace in the tab. Search for = and replace it with your initials.
This will convert all formulas to a text string. When you've copied
the tab into the new workbook, do the F&R for your initials and replace
it with =. This converts the text back into dynamic formulas.

The caveat: use something other than your initials if your initials
will be construed by Excel as part of the name of an existing tab.

 

[Rich Ulichny]

Thanks. That suggestion worked just fine. Formulas looked a bit messy at
first but then when I replaced my initials with = sign, things were back to
normal .

Rich

 

[ShelbyMan]

This method does work. But, is there a way to do this without losing the
formatting? I had colors, and made some columns smaller etc.. and there
disappeared whenI did the formula conversion by the above method.


Thanks