Microsoft Office Tutorials and References
In Depth Information
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.
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.
Search JabSto ::




Custom Search