VBA to Automatically Refresh Pivot Table When a Change is Made to Source Data?

[hea_admin]

Hello,

I'm trying to find a way to auto refresh a pivot table each time a change is made to the source data. I'm using Excel 2007. The pivot table is on the same worksheet as the source data; but it would be no problem to move it to its own worksheet if required for the auto refresh.

I've been reviewing and attempting to apply the VBA code on this forum; however, I'm not well versed in VBA and so far no success.

Here is the code that I'm trying to use:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("Active").PivotTables("PivotTable2").RefreshTable
Application.ScreenUpdating = True
Application.EnableEvents = True
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
End Sub
​

Any help would be very much appreciated.

Thank you,
Gail