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




Custom Search