Making major changes to the workbook

If you require to make changes to a workbook such as moving a view, copying a sheet, or deleting a sheet, there are some things you must consider first. Excel Services uses named ranges for the dimensions and the data area in views. These named ranges are required and should not be deleted.

Named ranges can be moved.

Excel Services finds the sheet that a view is on from the RefersTo property of the workbook name: cs + <view name> + Anchor. For example, the name in the workbook for a view named Expenses would be csExpensesAnchor. If you rename a sheet, Excel Services knows the new sheet name because the reference for cs + <view name> + Anchor is updated when the sheet name is changed. If you delete the sheet, the workbook's RefersTo property has an error #REF in it. The error #REF can be deleted.

When a sheet is copied, the names are copied along with the sheet. Since there can only be one instance of a workbook name, the other workbook names are prefaced with the sheet name in the names collection. The original workbook name without the sheet name preface remains. The RefersTo property of the name points to the first sheet that contains the name.

For example, you have a sheet named Sheet1 with a range named MyRange. You copy the sheet and insert the copy as the last sheet in the workbook. MyRange still refers to the cells on Sheet1.

If instead you insert the sheet as the first sheet in the workbook (in front of Sheet1), MyRange now refers to the cells in sheet Sheet1 (2).

The sheet with the active view is always the sheet that is earliest in the list of sheets. If you copy the sheet before the original sheet, the active view is now on the copy. If you copy the sheet after the original sheet, the original sheet maintains the active view.