Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Copying Sheets Between Workbooks Can Create Unexpected Links


View products that this article applies to.

Symptoms

When you copy a sheet to a target workbook, you may establish a link to the source workbook that will remain even after the copied sheet is deleted from the target workbook.

Examples

If you copy a dialog sheet to another workbook and the dialog sheet contains any of the following
  • check box
  • option button
  • list box
  • combo box
  • scroll bar
  • spin box
and the Input Range or Cell Link box on the Control tab of the Object dialog box contains a reference, then Excel will create a link to the source workbook. Additionally, if the Input Range or Cell Link box references a named range, then the named range will be added to the target workbook's Define Name list.

If you copy a worksheet or macro sheet that contains a formula that references another worksheet in the source workbook, then Excel will create a link to the source workbook. Additionally, if the formula that references another worksheet in the source workbook contains a named range, then Excel will add the named range to the target workbook's Define Name list.

Copying sheets containing charts to a new workbook will also create a link.

↑ Back to the top


Workaround

To delete the unwanted link, follow these steps:
  1. Switch to the target workbook (the workbook which contains the link) and save it.
  2. On the Edit menu, click Links.
  3. In the Source File list, click the link, and then click Change Source. Locate the target workbook in the Change Links dialog box and click OK.
Or, for more information about an alternate workaround, please see the following article in the Microsoft Knowledge Base:
188449 XL: Delete Links Wizard Available

↑ Back to the top


More information

This behavior is by design. Any time a sheet (such as a worksheet, a dialog sheet, or a macro sheet) that contains a named range is copied to another workbook, Excel adds the referenced named range to the Define Name list, as a global name that defines the link to the original workbook.

↑ Back to the top


References

For additional information, please see the following articles in the Microsoft Knowledge Base:
106718 XL: Unexpected Results When Pasting Linked Text Box

124232 XL: Visual Basic Macro to Break Chart Links

119084 XL: Macro to Delete Link Between List Box and Range of Cells

138619 XL: Macro to Delete Defined Names with Links

↑ Back to the top


Keywords: KB136314, kbprb

↑ Back to the top

Article Info
Article ID : 136314
Revision : 4
Created on : 8/17/2005
Published on : 8/17/2005
Exists online : False
Views : 37