Page 387 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
PivotTables and PivotCharts

Resetting a PivotTable to Its Original Position
After you’ve manipulated a PivotTable for a while during a presentation, it’s very easy to for-
get the original arrangement of fields in the PivotTable. If you want to reset a PivotTable to its
original condition, all you need to do is re-order the fields. Doing it by hand might be prob­
lematic when you’re trying to concentrate on your message, so it makes sense to write a
macro that re-creates the original layout. The next procedure sets the PivotTable in Edit-
Pivot.xls to the layout shown in Figure 16-8.

Sub ResetPivotTable()
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Month")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Week")
.Orientation = xlRowField
.Position = 2
End With

With ActiveSheet.PivotTables("PivotTable8").PivotFields("Day")
.Orientation = xlRowField
.Position = 3
End With

With ActiveSheet.PivotTables("PivotTable8").PivotFields("Hour")
.Orientation = xlColumnField
Chapter 16
.Position = 1
End With
End Sub

Note Notice that the code moves the fields into position in order, so that the field in the
Row area’s position 1 goes in before the field in position 2.

Recording and Restoring Arbitrary PivotTable Positions

Specifying the exact location of each field in a PivotTable is great if you know the desired lay-
out of your PivotTable, but how do you remember when you’re playing around with a Pivot-
Table and you happen upon an arrangement you love? The old way to remember the layout
was to write down the order, keep the paper handy, and reconstruct the PivotTable by hand.
The new way is to use the RecordPosition macro to write the field order to a group of cells in
the active worksheet. Again, the macro is specific to the PivotTable on the EditPivot.xls work-
book’s PivotTable worksheet.

361