Pivot table auto-refresh when data changes?

Posted by Jennifer Johnson on November 03, 2001 2:34 AM

Can someone help me? I'd like to know if it's possible to have a pivot table automatically refresh itself after data is changed in the source table. I have seen previously on this site where it is possible to do this when the worksheet gets re-activated, but is there such a way to have it refresh as soon as the data is changed? My source table and pivot table are on the same sheet. Thanks so much if you know a way.

Jennifer J.

Posted by Dank on November 04, 2001 12:51 AM

Hello Jennifer,

Try this:-

Right click the tab of the worksheet that contains your source data and pivot table and select View Code. This will present you with a code module which is associated with this particular sheet. Type this:-

Private Sub Worksheet_Change(ByVal Target As Range)
Me.PivotTables(1).RefreshTable
End Sub

It assumes that there is only one pivot table on your worksheet. If there is more than one then let me know.

Regards,
Daniel.

Posted by Jennifer Johnson on November 04, 2001 2:10 AM

Thank you Daniel, just what I was looking for.