Pivot Table - Add calculated field using field number (index) vs n

P

Preston Bandy

I'm building out some financial reports where the titles change every month -
consequently changes my calculated field refrences. The sequence or position
of the fields doesn't change & the ideal solution for me would be to
reference the pivot fields with an index number. I've made several attempts
and done quite a bit of searching with no luck. Working with Excel 2007. My
code is below and the code that doesn't work is inside the With block.
Basically, I'm creating the Pivot Table using VBA - inside the with block not
displayed I'm adding Page Fields, Row Fields and quite a few Data Fields.

Dim PTCache As PivotCache
Dim PT As PivotTable
Application.Calculation = xlManual

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:="PVTData")

Set PT = PTCache.CreatePivotTable(TableDestination:=Sheet1.Range("A21"),
TableName:="PivotTable1")

With PT

ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add
"BudgetYTD", "=" & PT.PivotFields(71).Name & "+" & PT.PivotFields(72).Name &
"+" & _
PT.PivotFields(73).Name & "+" & PT.PivotFields(74).Name & "+" &
PT.PivotFields(75).Name & "+" & PT.PivotFields(76).Name & "+" & _
PT.PivotFields(77).Name & "+" & PT.PivotFields(78).Name & "+" &
PT.PivotFields(79).Name & "+" & PT.PivotFields(80).Name & "+" & _
PT.PivotFields(81).Name & "+" & PT.PivotFields(82).Name, True

ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields("BudgetYTD"), "Sum of
BudgetYTD", xlSum

End With
 
Top