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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top