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
"Dave O" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[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