Posted by: Yosef B. | April 30, 2013

The Many Ways to Refresh an Excel Pivot Table


Ed. Note: Click on any picture to view full size

There are relatively few things as aggravating as spending hours trying to track down why the calculations in your PivotTable do not match your data than to finally realize that you forgot to refresh your PivotTable…

Here are a few tips & tricks for automatically refreshing your PivotTables.

Automatically Refresh PivotTables Upon Opening Excel Workbook

This first trick shows you how to set your PivotTable to refresh all calculations automatically as soon as you open your workbook. The obvious drawback is, if you change any of your data after you open the workbook, you will still have to refresh the PivotTables to reflect the new data.

To enable this feature, right-click anywhere on your PivotTable & select “PivotTable Options”

PivotTable Options

Next, select the “Data” tab and then select the “Refresh data when opening the file” option. Click “OK” to save your selection.

PivotTable Options

Write a Macro

This second option is very easy to implement and will refresh your PivotTable every time you select the worksheet tab that contains the PivotTable. Unfortunately, I have not figured out how to make your PivotTable refresh every time you change data in your data source (if anyone has a better macro, please share in the comments below!).

The drawback to this trick is, you have to leave the PivotTable tab and then return to it to force a refresh. Still – if you have your data on a different tab & you’re going back and forth anyways to check your calculations than this should help prevent our original aggravating scenario…

Right click on the sheet name where you placed the PivotTable and select “View code”

Excel View VBA Code

Copy the following code into the resultant code window (Note: if you copy & paste, you may have an error because the double-quote symbol does not always paste correctly into Excel – if you have a problem, try replacing all the double-quotes with new ones in the code editor.)


Private Sub Worksheet_Activate()
    Sheets(“PivotTable”).PivotTables(“PivotTable1″).RefreshTable
End Sub


You will need to change this code to match your specific information as follows:

  • “PivotTable” is the name of the worksheet tab where your PivotTable resides
  • “PivotTable1″ is the name of your PivotTable

Click the “Save” button. (Highlighted red in the picture below)

PivotTable Refresh Code

To find the name of your PivotTable, simply click anywhere on your PivotTable and then look at the name listed in the PivotTable section on the “Options” Tab on the special “PivotTable Tools” area of the ribbon. (In my example picture below, the PivotTable name is PivotTable4).

PivotTable Name on Ribbon

Questions or related tips/tricks? Let me know below!

About these ads

Responses

  1. Hello.

    PivotTable Tools > Options > Refresh All button on the ribbon:

    Refresh All = Ctrl + Alt + F5
    Refresh = Alt + F5

    You wrote:

    “The drawback to this trick is, you have to leave the PivotTable tab and then return to it to force a refresh”

    The above shortcuts would solve the leaving the pivot table issue. To solve the core problem, you really need to capture the Worksheet_Change() event that Excel fires in the background when a cell or range changes — assuming data is in the same workbook. Then you can determine if the change was in the data range or table that backs the Pivot Table, and if so, force the refresh in the event handler – Worksheet_Change(). Alternatively, you could set up other event handling code to act as a listener to changes in the data source object. Same idea as Worksheet_Change() but handled in a separate event handler – not Worksheet_Change() but same idea.

    If connected to an external data source you would have to do something else beyond the scope of your post.

    Hope the above helps.

    Bobby

  2. How do you change the code to update more than one pivot table on a sheet? I’ve tried entering a second PT name, copying the whole code and can’t get it to work. Obviously do not know how to write VBA code, so am looking for detailed example. Thanks.

    • Hi Paula – thank you for the question!

      It sounds like you have tried what I’m about to tell you so I’m not sure what exactly is going wrong. The way I update multiple pivot tables on the same sheet is with the following code:


      Private Sub Worksheet_Activate()
      Sheets("Name of Sheet1").PivotTables("PivotTable1").RefreshTable
      Sheets("Name of Sheet1").PivotTables("PivotTable2").RefreshTable
      Sheets("Name of Sheet1").PivotTables("PivotTable3").RefreshTable
      End Sub

      As you said you tried, you simply copy the line and modify the pivot table name. If this is not working for you, could you post a copy of your code for me to look at?

      Thanks!

      ~Yosef

  3. […] some threads which may help. I don't have 2013, so can't solve it for you Update (refresh) data in a PivotTable – Excel – Office.com The Many Ways to Refresh an Excel Pivot Table | Excellent Tips […]

  4. Hi,

    Thanks for the script, its gonna come in handy if I can get it to work! When I paste in your macro code to auto refresh between tabs I get an error “Runtime error ‘9’: subscript out of range”

    Any ideas?

    The code I entered

    Private Sub Worksheet_Activate()
    Sheets(“Sheet4”).PivotTables(“PivotTable3).RefreshTable
    End Sub

    Excel 2013

    Any help at all is appreciated!

    Thanks!

    • Hi Gabe thanks for the feedback! have you ensured that your sheet4 & pivottable3 are named correctly? Also, not sure if this is only in your comment but it looks like you’re missing a quotation mark (“) after your pivottable3.

      • Yosef,

        Thanks for the quick reply. I was missing the quotation, I added it back in and now I get a “compile error: syntax error” Any ideas?

        Thanks!

      • Hi Gabe,

        I’m not sure what’s causing that error. 3 things to look for: A) If you copied & pasted the code from my website, it’s possible that the quotation marks did not paste correctly. Try deleting and retyping all quotes and see if that helps. B) In the VBA code editor, make sure that the sheet that you put your code in is Sheet4 to match the code that you posted (i.e. Sheets(“Sheet4″)). C) Make sure that you have indented the code properly, e.g. the “Sheets(“Sheet4”).PivotTables(“PivotTable3).RefreshTable” line should be indented one tab inside the other two lines.

        If you’re still throwing an error – let me know what version of Excel you’re running & I’ll try researching it some more.
        Hope this helps!

      • Again thank you sooo much for the help! Got it working now, like you said the double quotes had to be replaced, cant just paste those in. Working like a charm now!

        Thanks again!!

      • Great! Glad it worked! Let me know if you have any other Excel questions.

        ~Yosef

  5. Here is a formula that auto sizes the pivot table every time a refresh is done.

    =Table!$F$15:OFFSET(Table!$F$15,COUNTA(Table!$F:$F)-9,COUNTA(Table!$15:$15)-3)

    Table! is the name of the worksheet. F15 is the top LH start of the pivot table. I have to mess about with the -9 and -3 value to get the exact bottom RH corner. I didn’t create the formula but it works for me.

    • Hi Alan – thanks for sharing! I took a look at your formula & I don’t understand what you’re trying to do. First off – is this supposed to be entered as an array formula? Second – where are you supposed to put this formula? It looks like it could be used to reference a pivot table but I’m not sure how it would force a pivot table to refresh?

      Thanks! ~Yosef

  6. Here’s what I’ve got, use in the worksheet not a module:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address Sheet1.PivotTables(“Pivot Table 1″).TableRange1.Address Then
    Sheet1.PivotTables(“Pivot Table 1″).RefreshTable
    End If

    End Sub

    • Typo.. missed the ..
      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Address Sheet1.PivotTables(“Pivot Table 1″).TableRange1.Address Then
      Sheet1.PivotTables(“Pivot Table 1″).RefreshTable
      End If

      End Sub


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

Join 150 other followers

%d bloggers like this: