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.

This behavior is by design of Microsoft Excel.

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.

MORE INFORMATION

Example

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:
   
   
   1. In step 1 of the wizard, click Next.
   
   
   
   2. In step 2 of the wizard, click Next.
   
   
   
   3. 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.
   
   
   
   4. 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.

Additional query words: XL97

Keywords : xllist xlpivot
Version : WINDOWS:97; MACINTOSH:98
Platform : MACINTOSH WINDOWS
Issue type : kbprb