Page 389 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
PivotTables and PivotCharts

Table 16-7. Numerical Values of XlPivotFieldOrientation Constants
Constant Numerical Value
xlColumnField 2
xlDataField 0
xlHidden 0
xlPageField 3
xlRowField 1

Caution When you write the values representing a PivotTable’s layout to a worksheet,
you’ll need to be sure there’s a blank row below the last entry to ensure the restore macro
will run correctly.

Now comes the moment of truth. You’ve recorded the PivotTable layout using the RecordPo­
sition procedure, but it’s time to see if you can use that data to re-create the layout you
recorded. To test the restoration procedure, change the layout of the PivotTable on the Pivot-
Table worksheet of EditPivot.xls and run the ResetFromRecorded macro.

Important For this macro to run correctly, the active cell needs to be on the worksheet
that contains the recorded position data. This macro also assumes you're using the Pivot-
Table in the EditPivot.xls workbook. If you want to use it on another PivotTable, you'll need
to change the With statement so it reflects the worksheet name (PivotTable in the example)
and identifier of the PivotTable (PivotTable8 in the example).
Chapter 16
Sub ResetFromRecorded()

Dim myRange As Range
On Error Resume Next

Set myRange = Application.InputBox(Prompt:="Please click the cell _
that contains the Field Name column heading.", Type:=8)
myRange.Select

Do While ActiveCell.Offset(1, 0).Value <> ""
ActiveCell.Offset(1, 0).Select
With Worksheets("PivotTable").PivotTables("PivotTable8") _
.PivotFields(ActiveCell.Value)
.Orientation = ActiveCell.Offset(0, 1).Value
.Position = ActiveCell.Offset(0, 2).Value
End With

Loop

End Sub

363