Microsoft Office Tutorials and References
In Depth Information
Figure 3-15: Copying a worksheet creates duplicated names.
This proliferation of names when copying a sheet is not only confusing; it can also result in errors that can be
difficult to identify. In this case, typing the following formula on the copied sheet displays the contents of cell
A1 in the copied sheet:
In other words, the newly created worksheet-level name (not the original workbook-level name) is being used.
This is probably not what you want.
If you copy the worksheet from a workbook containing a name that refers to a multisheet range, you also copy
this name. A #REF! error appears in its Refers To field.
When you copy a sheet to a new workbook, all the names in the original workbook that refer to cells on the
copied sheet are also copied to the new workbook. These include both workbook-level and worksheet-level
Copying and pasting cells from one sheet to another does not copy names, even if the
copied range contains named cells.
Bottom line? You must use caution when copying sheets from a workbook that uses names. After copying the
sheet, check the names and delete those that you didn't intend to be copied.
Name problems when deleting sheets