CalculatedItems Collection

  • Article
Excel Developer Reference

A collection of PivotItem objects that represent all the calculated items in the specified PivotTable report.

Remarks

A PivotTable report that contains January, February, and March items could have a calculated item named “FirstQuarter” defined as the sum of the amounts in January, February, and March.

Use the CalculatedItems method to return the CalculatedItems collection.

Use CalculatedFields(

index

), where

index

is the name or index number of the field, to return a single PivotField object from the CalculatedFields collection.

Example
The following example creates a list of the calculated items in the first PivotTable report on worksheet one, along with their formulas.

Visual Basic for Applications 
  Set pt = Worksheets(1).PivotTables(1)
For Each ci In pt.PivotFields("Sales").CalculatedItems
    r = r + 1
    With Worksheets(2)
        .Cells(r, 1).Value = ci.Name
        .Cells(r, 2).Value = ci.Formula
    End With
Next

See Also