copy sheets w/o link to source file
Posted by Laurel on May 09, 2001 2:28 PM
I have a multi-sheet workbook template with links between sheets (but no external ones). I want to create a new sheet in the template w/links to other sheets in the template and then put copies of this new sheet into files made with the original template that already have data in them. Whenever I try to copy a sheet to another file, it creates a reference to the source file and doesn't work right. I've got thirty files made from my template and they all need the same new sheet added with the same links to other sheets within their workbook file. Is there a way to make one "template" sheet and copy it to all of them and have the cell references only refer to the sheets within each workbook? This seems like it should be easy, but it's driving me nuts! Thanks, Laurel
Posted by Brian Deschene on May 10, 2001 10:26 AM
Laurel:
I think this URL to article Q136314 in Microsoft's KnowledgeBase addresses your issue. I recently had to look this up to help an Excel 2000 user at my current client.
Luck,
Brian D.
Posted by Laurel on May 11, 2001 8:30 AM
Brian- Thanks for your reply. I thought I had figured it out yesterday- but not quite. Part of the problem is that when you copy a sheet, there are all these references to the old sheet in the formulas. I copied the text of the reference to the source file [file name within bracket] and pasted it into the "find" dialog box, then left the "replace" box blank and hit "replace all". This took every last reference out of the formulas and I started celebrating. So simple! However, when I looked at the Edit Links dialog box, the darn links are still there. So now, armed with your advice, I'll try to get rid of the phantom links. You'd think Excel would have a preference toggle for copying sheets to other workbooks that would give you a choice of linking or not linking. Thanks again, Laurel
