Microsoft Office Tutorials and References
In Depth Information
Resetting a PivotTable to Its Original Position
After you’ve manipulated a PivotTable for a while during a presentation, it’s very easy to
forget 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
EditPivot.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
.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
layout of your PivotTable, but how do you remember when you’re playing around with a
PivotTable 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
workbook’s PivotTable worksheet.
Search JabSto ::




Custom Search