Update a Pivot Table when the worksheet becomes active

[golfnut58]

I am new to Pivot Tables, but it appears that the pivot table data is static. When I create the Pivot Table with a Sum of Amount as data, if I change the input data on worksheet 2, the Pivot Table data remains as it was when I created it.
I have assumed that this is a function of a Pivot Table, so I created a macro that will refresh the Pivot Table. (Now that I have searched this forum I see there is a much easier way of doing the refresh in the macro than the method I am using......isn't there always...lol)
What I really want to do, is have the refresh occur automatically every time the Pivot Table sheet becomes Active. That way there is no chance of someone changing data in worksheet2 and then looking at the old Pivot Table. As soon as they come back to the Pivot Table sheet the refresh would occur.
I assume there is an event handler for Active Worksheet, but I'm not having any luck in locating it. I'm also assuming I could call my refresh Pivot Table macro upon this event.
Am I close ?

 

[SydneyGeek]

Hi Gordy,

Yes Pivot tables are static because they work off a cache, which needs to be refreshed when data changes. As you already have the code for refreshing the PT, you can use this:

Private Sub Worksheet_Activate()
  Call YourMacroName
End Sub

Right-click the sheet tab where you want it to live, View Code, and paste in there, then change the call to use your existing macro

Denis

 

[golfnut58]

Denis,
Thank you for that prompt response !
As Murphy's law would dictate, after an hour of fruitless searching, moments ago I had found the exact answer you just supplied.
Thanks again.

 

[SydneyGeek]

You're welcome!

Denis