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

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

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