Pivot Table refresh based on source data update.

[jordanbuchan359]

Hi,

I'm trying to use VBA to refresh a pivot table when its data source changes. However, to make things complicated, the data source is a look up table pulling data from another pivot table (lets call this the master pivot).

I first attempted to use worksheet_change, but unfortunately this only captures manual changes.. not formula calculations. I then attempted to use the Worksheet_Calculate:

Private Sub Workbook_Open()
    PrevVal = Sheet6.Range("G3").Value
End Sub

Private Sub Worksheet_Calculate()
    If Range("G3").Value <> PrevVal Then
        Worksheets("Pivots").PivotTables("Pivot2").RefreshTable
        PrevVal = Range("G3").Value
    End If
End Sub

This method does capture the formula change (verified with MsgBox), but fails as soon as I add in the code for refreshing the Pivot table:

Not sure how best to proceed with this. Any help would be appreciated!