The FixRefresh-PivotTable

  1. Right Click on your pivot table report tab
  2. Select the view code menu item
  3. Add the code snippet below (assuming that the Pivot Table is the first or only pivot table on the page) in the box
  4. Click on the Save button at the left of the window
  5. Close the code window.
  6. Every time you click on the worksheet tab, the table will refresh.
Private Sub Worksheet_Activate()
 Me.PivotTables(1).RefreshTable
End Sub

The Problem

I was helping my brother in law work on a master spreadsheet that listed the various jobs that were being worked on.  I created two pivot tables that filtered the jobs by the person who was working on it.  However, when the master list was updated, the pivot tables were not refreshing.  To work around it, he was exiting and then opening the file again.  Yuck!

What is Happening

The bit of code is telling the first pivot table on the worksheet tab to update when the worksheet is accessed (i.e. activated).

Hope that helps!

–Ben

Sources

Microsoft Technet Post by Hans Vogelaar