Excel Pivot Table Tip – 2 Ways To Automatically Refresh Your Pivot Tables

If you a a everyday person of Pivot Tables then you likely would love Excel to routinely replace them and refresh themselves. Of direction we can manually refresh them all or in my view in a work sheet with a few steps local

Right clicking on any cell in the facts and Hit Refresh OR
Clicking anywhere on certainly one of your tables
Hit -Options Tab-Data Group-
Hit Refresh for one table or Refresh All if more than one desk is on your work ebook
We can paintings a bit smarter although and get Excel to do the tough work for us in multiple approaches. The first way is to –

1. Refresh mechanically when the file opens.

This will simplest provide you with partial automation by teaching Excel to refresh the report when it opens. So, there may be no code on this approach, however we get Excel to do maximum of the work.

Right click on anywhere in your Pivot Table
Pivot Table Options
Data Tab
Tick Refresh Data When Opening The File
Whilst that is in part automatic, it method you have to set this setting on every of your Pivots. There is a better manner to automate this function.

2. Using a small little bit of VBA code.

This 2d manner is absolutely automatic and Excel does the paintings, but it does involve a small little bit of VBA programming. This is Visual Basic For Applications. All we need to do is insert a few strains of code into the worksheet. The code wishes to be placed within the non-public module of your work sheet.

To get to the Private Module of any Worksheet hit ALT +F11 to open up the Visual Basic Project Window. You then need to proper click on the sheet call tab and choose View Code.

If your work e book has simply the only Pivot Table then use this piece of code to refresh it.

Private Sub Worksheet_Activate()

Me.PivotTables(1).RefreshTable

End Sub

This will mechanically refresh a single Pivot Table as soon as the Work sheet is activated.

If you figure e-book has a couple of Pivot Table then use this piece of code beneath

Private Sub Worksheet_Activate()

Dim pt As PivotTable

For Each pt In Me.PivotTables pt.RefreshTable

Next pt

End Sub

This code will ensure that each one of your Pivots in your work sheet can be refreshed as soon as the paintings sheet is activated. Don’t forget to shut the Project Explorer window and keep once you’ve got finished coming into the code.

BJ Johnston has been a sophisticated Excel person for 15 years and is the author of that shares Excel suggestions and tricks with it’s enthusiastic contributors. To be a part of inside the discussion, wherein users are operating smarter and quicker with Excel sign up for the FREE newsletter and as an advantage get hold of a FREE eBook – 50 Top Tips and Tricks.