Calculated Items

You can add calculated items to a field using the Add method of the CalculatedItems collection. Say you wanted to add a new product—melons. You estimate that you would sell 50% more melons than mangoes. This could be added to the Table created by CreatePivotTable using the following code:

Sub AddCalculatedItem()

'Add a new calculated item to the

'PivotTable produced by CreatePivotTable

With ActiveSheet.PivotTables(1).PivotFields("Product")

.Calculatedltems.Add Name:="Melons", Formula:="=Mangoes*1.5" End With

End Sub

This would give the result in Figure 7-13.

?< ■ :m ab+51. -:irrn

_ »

*

A

B

C

□ E

F

G

-

2

4

Sum of NumberSold IColunm Labels T Row Labels MApples

Mangoes Oranges Pears Melons

Grand Total

8

Pradesh Roberts Smith

10462 15068 13202 8551

14904 13018 16376 12222

13029 14875 13619 11688 13541 10075 14561 13583

22356 19527 24564 18333

75626 72920 77758 67250

10

Grand Total

47283

56520

54770 50201

84780

29Î554

11

h

' h Sheet*

=h;eid Sheet! Sales SiH I

iii!

Ml

Figure 7-13

You can remove the Calculatedltem by deleting it from either the CalculatedItems collection or the Pivotltems collection of the PivotField:

Sub DeleteCalculatedItem() 'Delete an item from the 'PivotTable produced by CreatePivotTable 'and AddCalculatedltem

With ActiveSheet.PivotTables(1).PivotFields("Product")

.PivotItems("Melons").Delete End With

End Sub

0 0

Post a comment