XL: Copying Sheets Between Workbooks Can Create Unexpected Links
ID: Q136314
|
The information in this article applies to:
-
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
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.
WORKAROUND
To delete the unwanted link, follow these steps:
- Switch to the target workbook (the workbook which contains the link)
and save it.
- On the Edit menu, click Links.
- 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:
Q188449 : XL97: Delete Links Wizard Available on MSL
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.
REFERENCES
For additional information, please see the following articles in the
Microsoft Knowledge Base:
Q106718 : XL: Unexpected Results When Pasting Linked Text Box
Q124232 : XL: Visual Basic Macro to Break Chart Links
Q126093 : XL: Macros to Delete Formula Links
Q119084 : XL: Macro to Delete Link Between List Box and Range of
Cells
Q138619 : XL: Macro to Delete Defined Names with Links
Additional query words:
Keywords : kbole kbdta
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:4.x,5.0,5.0c,7.0,97; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbprb
Last Reviewed: June 24, 1999