• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA/Macro: PivotCache.Refresh when value of a formula in a range changes

alanmotta

New Member
Hello to everyone and thanks in advance for the help!

I am having the following problem.

I have this range (AE7:AJ23) which reads data from another pivot table. This data range (AE7:AJ23) is the Data Source for the pivot table to the right. I am trying to write a code that refreshes pivot table to the right when I filter values from the original pivot table (VLOOKUP formula).

The code that i have for now is the following:

>>> use code - tags <<<
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("AE7:AJ26")) Is Nothing Then

ActiveSheet.PivotTables("CS_EVOLUÇÃO_CAT").PivotCache.Refresh

End If
End Sub

72643
 
Last edited by a moderator:
alanmotta
As You are asking VBA/Macros ... this thread should be opened to there.
... now moved in correct forum.
As a new member, You should reread Forum Rules.
 
alanmotta
As You are asking VBA/Macros ... this thread should be opened to there.
... now moved in correct forum.
As a new member, You should reread Forum Rules

Thanks for the help! First time here! Sorry for the trouble.
 
alanmotta
Over 90% sure that Worksheet_Change won't work as You've had an idea.
You should 'test' which of below will work better with Your idea:
Screenshot 2020-12-23 at 18.23.57.png
 
Use the Pivottableupdate event handler on the other sheet, so that when it updates its pivot you can update the other with the likes of:
Sheets("Pivot Cat-Subcat").PivotTables("CS_EVOLUÇÃO_CAT").PivotCache.Refresh
 
Back
Top