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

  • Thread starter Thread starter Rich Ulichny
  • Start date Start date
R

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
 
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.
 
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
 
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
 
Back
Top