Page 388 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out

Note You need to make sure the active cell is in a location where there will be room to
paste the position data this macro creates. To that end, it’s a good idea to create a new
worksheet, perhaps named RecordedPositions, to save these layouts.

Sub RecordPosition()

Dim pvtMyField As PivotField
Dim i As Integer

i=1
ActiveCell.Value = "Field Name"
ActiveCell.Offset(0, 1).Value = "Orientation"
ActiveCell.Offset(0, 2).Value = "Position"
With Worksheets("PivotTable").PivotTables("PivotTable8")
For Each pvtMyField In .PivotFields

ActiveCell.Offset(i, 0) = pvtMyField.Name
ActiveCell.Offset(i, 1) = pvtMyField.Orientation
ActiveCell.Offset(i, 2) = pvtMyField.Position

i=i+1
Next
End With

End Sub
Figure 16-10 shows the results of running the RecordPosition macro against the default
arrangement of the PivotTable in EditPivot.xls.

Chapter 16

Figure 16-10. Excel records your PivotTable position for future use.
It’s important to realize that the values assigned to the Position property are represented
internally as numbers, not the Excel constants in the XlPivotFieldOrientation group. Table
16-7 lists the XlPivotFieldOrientation constants and their corresponding numerical values.

362