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


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
 
Ad

Advertisements

D

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.
 
R

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
 
Ad

Advertisements

S

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
 

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

Top