CalculatedItems Collection Object

Microsoft Excel Visual Basic

CalculatedItemsPivotItem
Multiple objects

A collection of PivotItem objects that represent all the calculated items in the specified PivotTable report. For example, 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.

Using the CalculatedItems Collection

Use the CalculatedItems method to return the CalculatedItems collection The following example creates a list of the calculated items in the first PivotTable report on worksheet one, along with their formulas.

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
		

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