VBA code to refresh data fields based on values in combo box: Problem with Calculated fields

[Puneeth]

Hi Guys,
I using the following code to refresh all the pivot tables in my workbook based on values in Combo box.
The code runs good for normal fields, but when i try to run this code for calculated fields only 1 pivot table is updated. Can anyone of you let me know how to work with this?
I want all the datafields to be updated when i change the value in a combo box.
Please find below the code which i am using to accomplish the same with errors in calculated field.


Private Sub ComboBox1_Change()


Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim pf As PivotField
Dim strField As String
Dim pfNew As PivotField
Dim strSource As String
Dim strFormula As String


strField = Range("c35")


On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False



For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables
For Each pf In pt.CalculatedFields
strSource = pf.SourceName
strFormula = pf.Formula
pf.Delete
Set pfNew = pt.CalculatedFields.Add(strSource, strFormula)
Next pf


For Each pf In pt.DataFields
pf.Orientation = xlHidden

Next pf



With pt.PivotFields(strField)
.Orientation = xlDataField
.Function = xlSum

End With

Next pt
Next ws




Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub