Autorefresh pivot table on cell change

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
137
I have a pivot table (Pivottable2) which I want to refresh any time the value in cell B3 is changed. I have used the code below but appears that I am doing something wrong.

The name of the macro to refresh the pivot table is "refresht"

Any help greatly appreciated

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3")) Is Nothing Then refresht
End Sub
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
137
All good I have found a solution to the issue.

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("B3")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,096
Messages
5,466,624
Members
406,493
Latest member
Hazem Hassan

This Week's Hot Topics

Top