Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table - Add calculated field using field number (index) vs n
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:=xlDatab ase, SourceData:="PVTData") Set PT = PTCache.CreatePivotTable(TableDestination:=Sheet1. Range("A21"), TableName:="PivotTable1") With PT ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.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").AddDataFiel d ActiveSheet.PivotTables("PivotTable1").PivotFields ("BudgetYTD"), "Sum of BudgetYTD", xlSum End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove a Calculated Field from the Pivot Table field list | Excel Discussion (Misc queries) | |||
Pivot table, IF function, calculated item versus calculated field | Excel Discussion (Misc queries) | |||
Creating a Calculated Field in a Pivot Table for same field | Excel Discussion (Misc queries) | |||
Creating a Calculated Field in a Pivot Table for same field | Excel Programming | |||
Pivot Table Formulas Calculated Item / Calculated Field | Excel Programming |