Copying Sheets Between Workbooks Can Create Unexpected Links

• Microsoft Excel for Windows, versions 4.x, 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel 98 Macintosh Edition

SUMMARY

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 a check box, option button, list box, drop-down box, combination list-edit box, combination drop-down edit box, scroll bar, or spin box, and the Input Range or Cell Link box on the Control tab of the Object dialog box contains a reference, a link to the source workbook will be created. 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 a link to the source workbook will be created. Additionally, if the formula that references another worksheet in the source workbook contains a named range, then the named range will be added to the target workbook's Define Name list.

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 and click OK.

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, the referenced named range is added to the Define Name list as a global name that defines the link to the original workbook.

REFERENCES

For additional information, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q106718
   TITLE     : XL: Unexpected Results When Pasting Linked Text Box

   ARTICLE-ID: Q124232
   TITLE     : XL: Visual Basic Macro to Break Chart Links

   ARTICLE-ID: Q126093
   TITLE     : XL: Macros to Delete Formula Links

   ARTICLE-ID: Q119084
   TITLE     : XL: Macro to Delete Link Between List Box and Range of Cells

   ARTICLE-ID: Q138619
   TITLE     : XL: Macro to Delete Defined Names with Links