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

Jimmytinfoil

New Member
Joined
Feb 11, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

1645628159599.png


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

1645627320048.png


Pivot

1645627744817.png



Any advice appreciated

Jimmy



 

Attachments

  • 1645627358274.png
    1645627358274.png
    21.6 KB · Views: 29

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,262
Messages
6,171,080
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top