Hi,

I have code to change the field values in all pivot tables on Sheet1
based on the values entered in some specific cells. On cell A2 I have
a formula and I need the code to be triggered when it recalculates so
it will change the pivot table field value. The problem is that the
code I have works perfectly when the value is manually entered but not
when it changes based on a formula. Also tried the
Worksheet_Calculate () event but I don't know how to prevent the
"Object Required" error.

Would greatly appreciate the help!!

Code is pasted below:

Private Sub Worksheet_change(ByVal target As Range)
Dim a(10, 2) As Integer
Dim fieldadd(10, 2) As String
fieldadd(1, 1) = "$A$2"
fieldadd(1, 2) = "Name"
Application.ScreenUpdating = False
If (target.Address) = fieldadd(1, 1) Then
For Each pt In ActiveSheet.PivotTables
For Each pt1 In ActiveSheet.PivotTables
pt1.PivotFields(fieldadd(1, 2)). _
CurrentPage = target.Value
Next pt1
On Error Resume Next
Next pt
End If
target.Select
Application.ScreenUpdating = True
End Sub