Don has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel VBA Intro Intermediate course
Excel Pivot Tables course
Pivot table refresh
Could you supply the VBA code needed for automated refresh of Pivot Tables.
Thank you
RE: Pivot table refresh
Hi Don,
Thank you for the forum Question.
You can auto refresh a pivot table by opening the visual basic editor (press ALT F11). In the project explorer in the visual basic editor double click the worksheet with the pivot table and type the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot table").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
You will need to amend the code to fit your pivot table. Change the worksheet name to the name of your worksheet and change the pivot table name to your pivot table name.
Then every time you change the source data the pivot table will refresh.
Please let me know if you have any problems with the code.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Pivot table refresh
Hi Jens,
Thanks for the code.
I have added it to the Workbook. The code is fine but it doesn't automatically become an active routine.
I can activate the code by creating an error in it. This allows me to edit it and step through using F8. The 'step through' works refreshing the Pivot Table for the change(s) I made to the source data.
If I amend the data source, again, I have to go through the 'step through' to refresh the PivotTable.
Is there anything I need to change in the Trust Centre to enable the code once it is created?
RE: Pivot table refresh
Hi Don,
Sorry yes I can see I made a mistake.
Try to do the steps below.
You can auto refresh a pivot table by opening the visual basic editor (press ALT F11). In the project explorer in the visual basic editor double click the worksheet with the pivot table and type the following code.
In the code below you need to change the sheet name ("Pivot") and the pivot table name ("PivotTable1") to your names.
Private Sub Worksheet_Calculate()
Sheets("Pivot").PivotTables("PivotTable1").RefreshTable
End Sub
Please let me know if you still doesn't work.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
