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
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