How the more complex pivot table works

The second CreatePivotTable procedure in Listing 17-2 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. The code then adds the following fields to the pivot table:

♦ Department: A row field

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. The code then adds four calculated items to compute the quarterly totals. By default, the calculated items are added to the right side of the pivot table, so additional code is required to move them adjacent to the months to which they refer. (For example, Q1 is placed after March.) Finally, the code changes the captions displayed in the pivot table. For example, Sum of Budget is replaced by Budget ($).

I created this procedure by recording my actions while I created and modified the pivot table. Then I cleaned up the code to make it more readable and efficient.

0 0

Post a comment