If you are using the Layout dialog in the user interface to build the pivot table, Excel does not recalculate the pivot table after you drop each field on to the table. By default in VBA, Excel calculates the pivot table as you execute each step of building the table. This could require the pivot table to be executed a half-dozen times before you get to the final result. To speed up your code execution, you can temporarily turn off calculation of the pivot table by using the ManualUpdate property:

PT.ManualUpdate = True

You can now run through the steps needed to lay out the pivot table. In the .AddFields method, you can specify one or more fields that should be in the Row, Column, or Page area of the pivot table:

1 Set up the row & column fields

PT.AddFields RowFields:=Array("Product", "Customer"), ColumnFields:="Region"

To add a field such as Revenue to the Data area of the table, you change the Orientation property of the field to be xlDataField.

0 0

Post a comment