Skip to content
+1-888-319-3663

EONE BLOG

Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports


Using the refreshable Excel Reports is a great option to be able to view your Microsoft Dynamics GP data in a tool we all love.  Whether they are the default refreshable ones that can be deployed from Microsoft Dynamics GP or ones you create using eOne’s Excel Report Builder, getting the list and then being able to build out the pivot tables, charts, graphs, etc can be a very powerful tool.

One thing that we have seen internally and have heard from customers is that when you refresh the data sheet, it isn’t automatically refreshing the data in the Pivot Tables.  To refresh the Pivot Tables, you have to go to each one and refresh it.

With the help of our friend Michelle, we were able to figure out how to get the Pivot Tables to automatically refresh.  She showed this tip on the main stage at GPUG Summit in Reno and I wanted to share it with you as well.

Here are the steps to get your pivot table to refresh when the data refreshes in your worksheet.

  1. Turn on the Developer tab in Excel if it is not already on.  To do this, go to File – Options.  Select Customize Ribbon and then mark Developer.

  2. Expand the Developer Tab in Excel.

  3. Select Visual Basic to open the VBA Project.
  4. Image 1
  5. Double click on the sheet with your data set.  This will open the code window for you to enter in the code.  
    Image 2
  6. Enter in the following code.  You will need to edit the SheetName and PivotTableName to those in your workbook.
    Private Sub Worksheet_Change(ByVal Target As Range)
        Worksheets(“SheetName”).PivotTables(“PivotTableName”).PivotCache.Refresh
    End Sub

    For example, in my workbook, the data is in AGED_PM_DETAIL, the Pivot Table is on Sheet1, and my Pivot Table is called PivotTable1.  You can find the name of your pivot table by selecting it and then on the Analyze tab, selecting Options.  Below is what mine looks like:
    Image 3
  7. Save the code by going to File – Save.

Hopefully this helps in refreshing your Excel Reports so that you don’t have to manually refresh your Pivot tables after refreshing the data worksheet.

62 Comments

  1. Troy on March 11, 2016 at 6:35 am

    This is a genius technique.

  2. Sergio on April 5, 2016 at 7:32 am

    Awesomeness!!! thx!

  3. Omar on April 21, 2016 at 8:38 am

    I tried to follow the steps mentioned above but I keep getting a debugger screen saying that the code needs to be debugged. I double triple checked the sheet name and pivot table name and they all match. What seems to be the problem here?

    • Dennis Cook on June 29, 2016 at 2:55 pm

      I have the exact same problem, keep getting the error “run-time error ‘9’ subscript out of range”

      • Jason on April 25, 2018 at 8:11 am

        Double quotation mark must to be the vertical lines. Copy-pasted from web may have inserted the wrong ones. Retype them with you keyboard.

    • Lauren on June 13, 2018 at 12:54 pm

      make sure the name of your sheet and the name of your pivot do not have spaces in them. Use _ instead. You may have to rename stuff and then update your code accordingly.

  4. Clau on May 24, 2016 at 4:00 pm

    Thank you so much! What would be the correct formula if you want to add multiple pivot tables to be refreshed?

    • Michiel on April 21, 2017 at 6:22 am

      That would be:

      Private Sub Worksheet_Change(ByVal Target As Range)
      Worksheets(“SheetName”).PivotTables(“PivotTableName”).PivotCache.Refresh
      Worksheets(“SheetName”).PivotTables(“PivotTableName2”).PivotCache.Refresh
      Worksheets(“SheetName”).PivotTables(“PivotTableName3”).PivotCache.Refresh
      Worksheets(“SheetName”).PivotTables(“PivotTableName4”).PivotCache.Refresh
      End Sub

  5. Jade on July 9, 2016 at 1:39 am

    Tried adding this code (I’ve got 3 sheets & 3 pivots in one file) when I add data into the worksheet I get a debugger

  6. Healer on August 19, 2016 at 4:04 pm

    I have the same problem.

  7. Andy on September 6, 2016 at 10:26 am

    Same problem as Jade & Healer. Clicked Refresh & nothing happens.

  8. Throwaway on September 9, 2016 at 11:01 am

    I had the debugger problem as well.

    The problem is that this method does not work when the pivot table and the data source are on the same worksheet. They have to be on separate worksheets for this to work.

    The code should be entered into the worksheet with the data set. In the code, you should write the sheet that the pivot table is in.

    Does anyone know how to make it work for when the data set and pivot table are in the same worksheet?

    • John on March 6, 2019 at 4:43 am

      I tried this using excel 2010 but although the data refreshes automatically, the pivot table does not, unless I switch backwards and forwards between sheets, and then it works. Problem is the workbook in want this to work on needs to be left unattended.

  9. Zsolt on September 24, 2016 at 6:08 pm

    a possible reason for the “subscript out of range” error is that you delete existing data before refreshing/populating the data for the pivot table. in this case Excel senses the change, initiates the refresh, but there’s no underlying data to refresh with. a possible workaround for that problem is that you add the PivotCache.Refresh or RefreshTable instructions to the end of your data update macro

  10. Garrett on October 11, 2016 at 11:43 am

    When you copy-paste the VBA code, manually replace the pasted quotes with double quotes (change ” to “). This solved the subscript out of range problem for me.

    • Alan on February 17, 2017 at 12:37 pm

      Thanks Garrett – changing the quotes solved my debugging issue!

    • Brad on February 20, 2017 at 12:53 pm

      YES! That worked. Thanks, Garrett

    • Jenny on April 19, 2017 at 12:49 pm

      Worked for me too. Thanks so much.

    • Dan on August 4, 2017 at 12:27 pm

      Thank you for that fix.

    • Dean Beach on September 22, 2017 at 8:36 am

      Changing the quotes worked for me!

      Thanks Garrett

  11. Gail on October 26, 2016 at 11:58 am

    Hi I was having the same issue with the out of range error so I read through all of your comments, and I thought maybe trying this would work..
    Since the issue is refreshing the cache on the Pivot Table:
    Rather than using the event of Worksheet_Change .. I tried setting it to PivotTableUpdate, and used the same code for refreshing the PivotCache..

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Worksheets(“Sheet1”).PivotTables(“PivotTable1”).PivotCache.Refresh
    End Sub

    I don’t get the error now, but I’m not sure if this is going to have the desired effect either..

  12. David on November 4, 2016 at 8:56 am

    Brilliant, thanks.

  13. Rajeev Saptarshi on November 9, 2016 at 2:55 am

    Garretts solution worked …. thanks

  14. Sherilyn on December 6, 2016 at 3:22 am

    Hello, this does not resolve my problem but maybe I have not understood how to use it properly. I have three sheets, one with a data table (but where the values are dynamic depending on 2 other variables), one with the 2 variables to define, one with the pivot table but using a slicer to filter.
    I have done the above instructions and when I change my variables (which then changes the values in my data table), and I choose the field in the slicer, the value is not updated. I thought the pivot table was supposed to refresh automatically if any of the values changed in the data table? Thanks for your help.

  15. David Taveras on April 24, 2017 at 7:19 am

    I am getting a run-time Error ’13’: Type Mismatch

    Here is teh code i am using:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets(Pivot).PivotTables(A).PivotCache.Refresh
    End Sub

    This error come up regardless if i enter letters or numbers.

    What could my issue be?

    Thanks,

  16. Randy Nevers on April 24, 2017 at 5:25 pm

    Thanks for this information, works great!

  17. DougF on May 26, 2017 at 4:08 pm

    try this

    Dim PT as pivot tables
    For Each PT In WS3.PivotTables
    PT.PivotCache.Refresh
    Next PT

    • DougF on May 26, 2017 at 4:19 pm

      Sorry that should have been
      Dim PT as pivottable
      For Each PT In sheets(“sheet1”).PivotTables
      PT.PivotCache.Refresh
      Next PT

      I got impatient when the capta thing messed up

  18. Rabiul on May 27, 2017 at 9:40 pm

    If I have one database but more than one pivot table, how can i use it?

  19. Mnath on June 5, 2017 at 11:41 am

    Is there a way to update the pivot table when the main data sheet expands but columns and rows both?

  20. DougF on June 6, 2017 at 11:00 am

    The following will update all pivot tables in a workbook, I believe the table if not already expanded will expand when the pivot cache it refreshed.
    Dim PT As PivotTable
    Dim WS As Worksheet
    For Each WS In ThisWorkbook.Worksheets
    For Each PT In WS.PivotTables
    PT.PivotCache.Refresh
    Next PT
    Next WS

    • Stephen on July 14, 2020 at 5:54 am

      Bit slow but works brill, thanks

  21. Todd D on June 6, 2017 at 2:08 pm

    My pivot table data source is a table, how would you set up the vba to have the pivot table update when the data in the table changes?

  22. David B on June 21, 2017 at 12:57 pm

    Garrett’s solution is critical. If you are cutting and pasting from the web page change the double quotes manually to what your keyboard types. The one on the webpage looks good, but causes debugger errors. Thank you Garrett!

  23. Aayan on July 17, 2017 at 6:08 am

    Hi,

    below the code i m using for my worksheet to get refresh but problem is old data still remain in cache from the old data source

    how can get rid of that??

    Private Sub CommandButton1_Click()

    On Error GoTo 0
    On Error Resume Next
    Sheets(“Top Value”).PivotTables(“PivotTable6”).PivotCache.Refresh
    Sheets(“Top Value”).PivotTables(“PivotTable6”).PivotFields(“REQ#”).ClearAllFilters
    With Sheets(“Top Value”).PivotTables(“PivotTable6”).PivotFields(“REQ#”)
    .PivotItems(“REQ#”).Visible = False
    .PivotItems(“(blank)”).Visible = False
    .PivotItems(“#REF!”).Visible = False
    End With
    Sheets(“Top Value”).PivotTables(“PivotTable6”).PivotCache.Refresh

    Range(“A1”).Select

    End Sub

    plz help

  24. mayank khanna on July 17, 2017 at 7:27 am

    I want to load the particular columns data into ms access from excel sheet.
    Can someone let me know how to do this in VBA?

  25. Deanius on July 31, 2017 at 3:21 am

    Garrett you little Genius. Thanks

  26. Ruth M on October 19, 2017 at 2:44 am

    Hi Garrett,

    Just to confirm – with this formula, is the Pivot Table supposed to change automatically when I update my source data? i.e. I don’t have to manually refresh the Pivot table?
    If not, which formula am I supposed to use.

    thanks
    Ruth

  27. Rob on October 22, 2017 at 11:38 am

    I am using the code above but get a VBA runtime error that says the Pivottable cannot be refreshed on a protected sheet. The problem is, the sheet is NOT protected. Any ideas?
    Thanks

  28. Vaishali on November 30, 2017 at 7:39 am

    I got the data by running the sas job, and for the same file I have created pivot table, used slicer and updated the graph using the pivot table. As it is weekly report, I need to run the sas job to get the data but when I go manually and refresh the pivot table sheet it refresh the pivot table but it spoil my slicer and also the graph. Please advise.

  29. Andrea on December 8, 2017 at 6:47 am

    I\\\’ve inputted this code but nothing is happening. I don\\\’t even get an error. Is there something obvious I\\\’ve done wrong?

  30. Rachel on December 8, 2017 at 1:47 pm

    I tried this today and nothing was happening for me as well..the VBA wouldn’t recognize it as a macro. But then when I practiced refreshing my Data it did give me the “Run-time error ‘9’” Subscript out of range”. When I debug it, it highlights the second line:

    Worksheets(“Sheet9”).PivotTables(“PivotTable2”).PivotCache.Refresh

    I’ve tried Garret’s solution and it didn’t seem to solve the problem. I went ahead and manually typed the whole code, and I’m still receiving the run-time error. Any ideas what else could be causing the issue?

    • Mark on December 8, 2017 at 9:18 pm

      I have the same exact issue as Rachel – hand typed everything and the error 9/subscript error still persists.

      • Caro on December 13, 2017 at 5:49 am

        Me too!

  31. Caro on December 13, 2017 at 6:43 am

    I just fixed this; instead of referencing the sheet number, I\’ve referenced the name I gave the sheet.
    To do this I first renamed the sheet so that there were no spaces, so I don\’t know if that was causing a problem too.

    My sheet was called Sheet7 (Pivoted Unit v Risk) and I was using the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets(\”Sheet7\”).PivotTables(\”PivotTable8\”).PivotCache.Refresh
    End Sub

    It\’s now this:

    Sheet7 (Pivoted_Unit_v_Risk)

    Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets(\”Pivoted_Unit_v_Risk\”).PivotTables(\”PivotTable8\”).PivotCache.Refresh
    End Sub

    I hope that helps someone…

    • Caro on December 13, 2017 at 6:44 am

      please ignore the black slashes \\ – I’ve no idea why they’re in there!

    • Sheldon on May 30, 2018 at 10:37 am

      Thank you very much for that recommendation Caro! It helped me understand the syntax being employed by the macro (my first time implementing one), and it\’s a very clean code. After testing this proposed solution, I even tried to reference the generic sheet names in hopes of removing the underscores from my custom names, and it ran into the same issue. Your solution worked flawlessly for me, and is Much Appreciated!

    • Merlin on July 27, 2018 at 4:26 am

      Beautiful. Thanks!

    • Suv on January 22, 2020 at 3:20 pm

      Thank you ssooooo much.. I spent over 3 hours searching for why I am getting the debug issue..
      Initially even after changing Sheet name it gave me debug error..
      the second time i tried it, it worked 🙂 🙂

    • Mridul Kumar Sharmah on April 18, 2023 at 12:53 pm

      Thanks Caro!

      Your referencing method of Sheet Name instead of Sheet Number worked like a charm!

      Note: The VB code has to be on the data sheet and not on the sheet that contains the pivot table. I was also making this mistake.

  32. Justin on January 23, 2018 at 10:18 am

    For those who are getting errors and your data source (table) is on the same page as your PivotTable, it is because it’s creating an endless loop of sorts. You update your data source triggering a worksheet change, this triggers your Pivot to update causing another change, and so on. The referenced code will only work if your data is on a separate sheet from the PivotTable you want to refresh.

    If you want to keep your data source and PivotTable on the same page, you need to limit the scope of the what range will trigger a refresh. The easiest way to do this is to ensure your data is in a named Excel Table. You’ll know if you are in an Excel table usually by color and the addition of filters, but also if you click on a cell within the table you’ll see a “Table Tools” tab appear at the top right. Here you can find your Excel Table name. Then use the formula below to limit where Excel looks for cells being updated. Ensure you place it in the VBA code section for the sheet where your data and Pivot is located.
    ——-

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    Set KeyCells = Range(“EXCEL_TABLE_NAME”)

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then

    ActiveSheet.PivotTables(“PIVOT_TABLE_NAME”).PivotCache.Refresh

    End If
    End Sub

  33. ibrahim on February 2, 2018 at 4:24 pm

    Hello, when I entry the data from the excel’s form the pivot table does not refresh, anyone has any idea of how can I fix this problem?

  34. Andrew Sudworth on February 15, 2018 at 8:24 am

    Brilliant – just what I needed! 🙂

  35. Tim on March 7, 2018 at 3:35 pm

    This refreshes all the pivot tables as the data on the worksheet changes
    Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.RefreshAll
    End Sub

    • James Thompson on March 22, 2018 at 12:59 am

      Thanks!

    • Miles Wolbe on December 8, 2019 at 11:18 pm

      Thanks so much, Tim – this should be at the top.

  36. Rahul Sarkate on September 30, 2018 at 2:29 am

    Thank you so much, It\’s really help me a lot

  37. LP on October 16, 2018 at 11:57 am

    The VBA code works great unless the worksheet with the pivot table is protected. Is there a way around that?

  38. Peter Adams on May 29, 2019 at 8:46 am

    You are awesome!, wish I would have known about this a long time ago

  39. SatyaGupta on April 25, 2020 at 6:03 am

    Did all sort of things like changes double quotes and sheet name with underscore but having problem pop-up box with following message
    ‘Run-time error ‘1004’
    Unable to get the PivotTables property of the Worksheet class
    Please guide.
    Thanks

  40. granny on September 17, 2020 at 1:44 am

    Turn on the Developer tab in Excel if it is not already on. To do this, go to File – Options. Select Customize Ribbon and then mark Developer.

  41. Daniela Stefan on October 28, 2020 at 1:35 pm

    if the data extend the pivot table does not update.
    I use the below code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets(\”Pivot\”).PivotTables(\”PivotTable1\”).PivotCache.Refresh
    Worksheets(\”Pivot\”).PivotTables(\”PivotTable2\”).PivotCache.Refresh
    Worksheets(\”Pivot\”).PivotTables(\”PivotTable3\”).PivotCache.Refresh
    End Sub

Leave a Comment





RECENT POSTS


Meet the eOne Team at BC Tech Days 2023
Tech Tuesday: Building My First Matrix Report
Overcoming the Top Challenges of Zendesk Integration
Accessing Historical Dynamics GP Data in NetSuite: Using Popdock
Popdock's Top 10 New Features

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder
SmartList Builder 2013 New Feature of the Day: Auto Updating SmartList

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.