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:
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.
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.
But 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.
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!
=-=-=-=-=-=-=-=-=-=-=-=-=-