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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top