Posts by Matt Freeman

Matt Freeman · Mar 6th 2017

Re: VBA to Refresh Pivot Tables when Any Value in Range Changes

Thank you so much, Carim! It works perfectly.

Thank you also to 'NoSparks' for your contribution. Though the range does not contain formulas, I will keep this tip in mind.

Matt Freeman · Mar 3rd 2017

I'm trying to refresh my pivot tables when any number in the range of cells J22:L24 on Sheet1 is changed. The pivot tables are on the Sheet1 as well.

I tried this and nothing happened:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J22:L24")) Is Nothing Then
        Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
        Worksheets("Sheet1").PivotTables("PivotTable2").PivotCache.Refresh
        Worksheets("Sheet1").PivotTables("PivotTable3").PivotCache.Refresh
    End If
End Sub

I have been successful in refreshing the pivot tables via VBA when I make the refresh dependent on changing a single value named "Index" as shown here:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("Index").Address Then
   Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
   Worksheets("Sheet1").PivotTables("PivotTable2").PivotCache.Refresh
   Worksheets("Sheet1").PivotTables("PivotTable3").PivotCache.Refresh
End If
End Sub

Thanks for the help!

Matt