XL: Active Cell May Change When You Sort a PivotTable

• Microsoft Excel 97 for Windows
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, when you sort a PivotTable, the active cell may change, depending on the structure of the PivotTable.

This behavior may cause a problem if you sort the PivotTable while running a Visual Basic for Application procedure that assumes that the active cell has not changed as a result of the sort.

NOTE: This behavior is different from earlier versions of Microsoft Excel.

To see an example of this behavior, see the "More Information" section in this article.

CAUSE

This problem occurs because Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition use "structured selection" in PivotTables. This means that when you sort a PivotTable, Microsoft Excel moves the selection with the currently selected data. If the selection is not adjacent (not contiguous), Microsoft Excel selects the upper-right cell in the current selection.

WORKAROUND

You cannot change this behavior. However, if you are using a Visual Basic procedure to sort a PivotTable, try modifying the macro so that it does not assume that the active cell remains unchanged when you sort the PivotTable.

STATUS

This behavior is by design of Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition.

MORE INFORMATION

Example

To see an example of this behavior, follow these steps:

1. In Microsoft Excel, create a new workbook. Enter the following data in Sheet1:

         A1: Name        B1: Position   C1: Points
         A2: Dineen      B2: R          C2: 12
         A3: Kapanen     B3: R          C3: 27
         A4: Roberts     B4: L          C4: 25
         A5: Primeau     B5: C          C5: 22
         A6: O'Neill     B6: C          C6: 14
         A7: Sanderson   B7: L          C7: 13
   
   

2. Select cell A2. On the Data menu, click PivotTable or PivotTable Report.

3. Proceed through the PivotTable Wizard as follows:

       a. In step 1 of the wizard, click Next.
   

       b. In step 2 of the wizard, click Next.
   

       c. In the step 3 of the wizard, drag the Position field into the ROW
          area, drag the Name field into the ROW area, and drag the Points
          field into the DATA area. In the ROW area, double-click Position,
          select None, and click OK. Then, click Next.
   

       d. In step 4 of the wizard, select cell D10. Click Finish.
   

   The PivotTable lists the Position, Name, and total Points for each player.

4. Select cell D16. Click the Sort Descending tool on the Standard toolbar.

   In all versions of Microsoft Excel, the PivotTable is sorted. In Microsoft Excel versions 5.0 and 7.0, the active cell does not change. In later versions of Microsoft Excel, the active cell moves from cell D16 to cell F12 (the upper-rightmost cell in the selection) because the current selection is not an adjacent range of cells.

5. Select cell D16. On the Data menu, click PivotTable or PivotTable Report.

6. Drag the Position field out of the ROW area. Click Finish.

7. Select cell D16. Click the Sort Descending tool on the Standard toolbar.