Microsoft Office Tutorials and References
In Depth Information
Creating a More Complex Pivot Table
.DataBodyRange.NumberFormat = “0,000”
‘ Apply a style
.TableStyle2 = “PivotStyleMedium2”
‘ Hide Field Headers
.DisplayFieldCaptions = False
‘ Change the captions
.PivotFields(“Sum of Budget”).Caption = “ Budget”
.PivotFields(“Sum of Actual”).Caption = “ Actual”
.PivotFields(“Sum of Variance”).Caption = “ Variance”
End With
End Sub
How the more complex pivot table works
The CreatePivotTable procedure starts by deleting the PivotSheet worksheet if it already
exists. It then creates a PivotCache object, inserts a new worksheet named PivotSheet , and
creates the pivot table from the PivotCache . The code then adds the following fields to the
pivot table:
h Category: A report filter (page) field
h Division: A report filter (page) field
h Department: A row field
h Month: A column field
h Budget: A data field
h Actual: A data field
Notice that the Orientation property of the DataPivotField is set to xlRowField in the
following statement:
.DataPivotField.Orientation = xlRowField
This statement determines the overall orientation of the pivot table, and it represents the Sum
Value field in the Pivot Table Field list (see Figure 17-5). Try moving that field to the Column
Labels section to see how it affects the pivot table layout.
Next, the procedure uses the Add method of the CalculatedFields collection to create the
calculated field Variance , which subtracts the Actual amount from the Budget amount.
This calculated field is assigned as a data field.
Search JabSto ::

Custom Search