Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use VBA to update pivot table formulas
I have about 80 fromulas that I need to update
It's a lot easier to list the formulas in a worksheet the change them there does any one know Vba code that would update the formula in the pivottable from the worksheet -- Helping Is always a good thing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use VBA to update pivot table formulas
HI QuietMan,
I'm not sure what you mean by update. If all you need to do is take into account that the underlying data has changed for the Pivot Table then this should work: ActiveSheet.PivotTables(1).PivotCache.Refresh If you need to change the all formulas so the basic calculation is different then something like this would work: 'delete all existing formulas For Each PT In ActiveSheet.PivotTables For Each CF In PT.CalculatedFields CF.Delete Next Next 'add new formulas With ActiveSheet.PivotTables(1) .CalculatedFields.Add "Field1", "=C/B", True .PivotFields("FIELD1").Orientation = xlDataField .CalculatedFields.Add "Field2", "=C+B", True .PivotFields("FIELD2").Orientation = xlDataField .CalculatedFields.Add "Field3", "=C - B", True .PivotFields("FIELD3").Orientation = xlDataField End With End Sub If you need to change only certain formulas then address those fields by name - with ActiveSheet.PivotTables("PivotTable1") .CalculatedFields("Field2").StandardFormula = "=C-B" end with This changes Field2's calculation form c+b to c - b. Notice the double = signs after standardformula. Similar code works for calculated items. Regards, Mike "QuietMan" wrote: I have about 80 fromulas that I need to update It's a lot easier to list the formulas in a worksheet the change them there does any one know Vba code that would update the formula in the pivottable from the worksheet -- Helping Is always a good thing |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use VBA to update pivot table formulas
Thanks for the help Mike
I aslo found this way Sub Updates_Formulas_PivotTables() Sheets("Country").Select Fml_Count = Range("Fml_Count") For X = 3 To Fml_Count + 2 Sheets("Formulas").Select Application.Goto Reference:="R" & X & "C2" Fml_Name = ActiveCell Application.Goto Reference:="R" & X & "C3" ID = ActiveCell Sheets("Country").Select ActiveSheet.PivotTables("PivotTable1").CalculatedF ields(Fml_Name).StandardFormula = ID Next X End Sub Sub Add_Formulas_PivotTables() Sheets("Country").Select Fml_Count = Range("Fml_Count") For X = 79 To Fml_Count + 2 Sheets("Formulas").Select Application.Goto Reference:="R" & X & "C2" Fml_Name = ActiveCell Application.Goto Reference:="R" & X & "C3" ID = ActiveCell Sheets("Country").Select ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add Fml_Name, ID, True Next X End Sub -- Helping Is always a good thing "MIKE215" wrote: HI QuietMan, I'm not sure what you mean by update. If all you need to do is take into account that the underlying data has changed for the Pivot Table then this should work: ActiveSheet.PivotTables(1).PivotCache.Refresh If you need to change the all formulas so the basic calculation is different then something like this would work: 'delete all existing formulas For Each PT In ActiveSheet.PivotTables For Each CF In PT.CalculatedFields CF.Delete Next Next 'add new formulas With ActiveSheet.PivotTables(1) .CalculatedFields.Add "Field1", "=C/B", True .PivotFields("FIELD1").Orientation = xlDataField .CalculatedFields.Add "Field2", "=C+B", True .PivotFields("FIELD2").Orientation = xlDataField .CalculatedFields.Add "Field3", "=C - B", True .PivotFields("FIELD3").Orientation = xlDataField End With End Sub If you need to change only certain formulas then address those fields by name - with ActiveSheet.PivotTables("PivotTable1") .CalculatedFields("Field2").StandardFormula = "=C-B" end with This changes Field2's calculation form c+b to c - b. Notice the double = signs after standardformula. Similar code works for calculated items. Regards, Mike "QuietMan" wrote: I have about 80 fromulas that I need to update It's a lot easier to list the formulas in a worksheet the change them there does any one know Vba code that would update the formula in the pivottable from the worksheet -- Helping Is always a good thing |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Update | Excel Discussion (Misc queries) | |||
Pivot Table update | Excel Discussion (Misc queries) | |||
Pivot Table Update | Excel Discussion (Misc queries) | |||
Pivot Table update | Excel Discussion (Misc queries) | |||
PLEASE HELP...How do I update a pivot table with VBA? | Excel Programming |