Automatic Pivot Table Update with VBA Code (after Data source change)

[Jimmytinfoil]

Afternoon Everyone

Im sure you've heard it all before. I've been checking various examples of what Im trying to achieve and it just isn't working.

I have a data source (its actually formatted as a table; but it doesn't need to be) and a pivot table based on that data source

Key Ask:- If the data source changes I wanted the Pivot Table to Update automatically

Ive shared the XLS here on my google drive

Its a macro enabled XLS and Im using O365

The VBA code I have which is in the pivot table worksheet is as follows

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("pivot").PivotTables("pvt_fruit").PivotCache.Refresh
End Sub

Image 1

Where pivot is the name of the sheet where the pivot table is located
Where pvt_fruit is the name of the pivot table

Data

Pivot

Any advice appreciated

Jimmy

​

​

​

​

​

 

[RoryA]

The code should be in the module for the data sheet, not the pivot sheet. Personally, I wouldn't do it on every change - I'd probably use the Worksheet_Activate event of the pivot table sheet to update only when you switch to the pivot's sheet.

 

[Jimmytinfoil]

Thanks RoryA

I wont have too many changes to be honest. I was also researching this

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub

Which I guess depends on how simple or complex your data sources are etc