VBA to Refresh Pivot Tables when Any Value in Range Changes

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

**Carim** · Mar 4th 2017

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

Hello Matt,

You could test the following event macro

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("J22:L24")) Is Nothing Then Exit Sub
Application.EnableEvents = False
  ActiveWorkbook.RefreshAll
Application.EnableEvents = True
End Sub

Hope this will help

NoSparks · Mar 4th 2017

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

Do J22:L24 contain formulas ?
If so they will not trigger the Worksheet_Change event.

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.

**Carim** · Mar 6th 2017

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

Glad you could fix your problem ...:wink:

Thanks a lot ... for your thanks ...:smile:

VBA to Refresh Pivot Tables when Any Value in Range Changes

Participate now!

Share