<

Workbook link problems after copying tabs to a new workbook?

Published on
12,766 Points
4,666 Views
6 Endorsements
Last Modified:
Approved
Workbook link problems after copying tabs to a new workbook?
David Miller (dlmille)

Intro

Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original source workbook?  How did you resolve the issue?  Several things come to mind:

1.  You could edit your workbook’s links (figure 1) and change the source to the new workbook,
2.  You could do a FIND/REPLACE of the source workbook reference and clear that reference (figure 2)
3.  Independent of the option you leveraged, above, you also need to reference all your range names in the new workbook to ensure any cross-linkages to the source workbook were resolved (figure 3).
4.  And there were other potential issues you might have needed to address:  Table or PivotTable references, charts, etc.  The list could go on, depending on the complexity of your workbook.
Figure 1,2,3 - Resolving link issues after copying tabs to another workbookBut why did the linkage problem happen in the first place?  Unless you intentionally want that linkage to exist (pulling apart large workbooks with different functions is a common approach to offloading processing and making things more manageable/efficient), there are steps you could take to ensure the linkage problem does not happen in the first place.
 
Early this year, I helped out on 3 related solutions that gave me the idea that perhaps this problem is not so uncommon:
HOW TO MOVE OR COPY WORKSHEET TABS TO A NEW WORKBOOK AND AVOID LINKAGE ISSUES
If you want to create a new workbook from existing sheet tabs, and you want that new workbook to be “whole” in and of itself, without linkages to the source workbook, please consider the following:

If you MOVE sheets over, and those sheets don't interlink with any other sheets in the source workbook, even if they interlink with each other (are cross-linked), they can be moved one tab at a time or all at once.
However, if you COPY sheets to a new workbook and those sheets don't interlink with any other sheets in the source workbook, but they DO interlink with each other - and you copy them one at a time, the resulting worksheet copies in the new workbook will have linkages back to the source.  But, and here’s the big tip:  if you copy them (the sheet tabs) all at once, the resulting worksheet links will stay with the destination workbook, just as with the move operation.

It doesn't really matter whether you move OR copy worksheets to a new workbook, the links to the new workbook will be maintained in the new, destination workbook, IF all related, cross-linked tabs are either copied at the same time or are moved (either simultaneously or individually).  This applies to manual Excel manipulation, or to VBA coding – and it includes the added benefit of properly linked range names, as well.

RULE OF THUMB
While there’s a nuance between moving or copying interlinked worksheets to a new workbook (or even an existing, separate workbook), the rule of thumb should be to try to move or copy all the tabs at the same time.  We don’t need to remember 1) to MOVE tabs individually or together, 2) but to COPY tabs together to avoid problems, if we maintain a habit of moving or copying tabs in one operation, simultaneously.

You could just MOVE the relevant tabs over, check the new/existing destination workbook to ensure there are no links back to the source, save that file and close the original without saving.  That’s quite a few steps for me, especially if I’ve not had my coffee in the morning!

Or, click on the first tab to copy, and then hold the CTRL key down while selecting each of the other tabs that you want to copy at the same time (figure 4).  Don’t worry, Excel is fairly forgiving, if you select a tab you DIDN’T want, just click it again to de-select it.  As you select tabs, you should note that the tab coloration changes so you can see all the tabs that have been selected.  Then, when you’re ready, just right click your mouse on any of the selected tabs and perform your copy operation.
Figure 4-Select multiple tabs, THEN move/copy the worksheet tabs  
BACKGROUND
The reason the MOVE command works manually, even when the operation is done one tab at a time, is the fact that there is absolutely no conflict on where the link is because Excel has only the one reference point – it follows the sheet that is being moved.

However, when the COPY operation is performed one sheet at a time, TWO reference points (original and a copy) could have potentially existed for formulas that are referencing the sheet being copied, and Excel resolves that conflict by always pointing the copied sheet’s links back to the source workbook.  Again, selecting ALL relevant tabs, and THEN doing the COPY operation will create no conflict that Excel needs to resolve, resulting in a new (or separate existing) workbook without links back to the source.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
6
Comment
Author:dlmille
0 Comments

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Join & Write a Comment

This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month