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.

48 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.

  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?

  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

  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!

  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!

Leave a Comment




RECENT POSTS


A Helpful Guide to Integrating Cloud-Based Applications
Tech Tuesday: SmartConnect Versions You Should Use When Upgrading
Guest Consultant Blog & Video: Excel Report Builder to compare Microsoft GL Actual to Budget Dashboard
Integrations with Microsoft Dynamics NAV - See 'em in Action!
Finding and fixing the Unbalanced GL Entry Distribution. eConnect Error Number = 944

POPULAR POSTS


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

CATEGORIES

ARCHIVES

open all | close all

TAGS

#fargo CRM Demo Dynamics Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender Extender Enterprise GP GPUG integration integration manager Meet the Team Microsoft Microsoft dynamics crm Microsoft Dynamics GP MSDYNGP Office Relationships partners release SalesForce SalesForce.com scripting SmartConnect SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.

var optOutSettings = { text: 'Our website uses cookies to offer you a better browsing experience and personalize content.    ' + ' Privacy Policy ' + '    Disable Cookies', optedOutText: 'Unnecessary cookies for this website have been disabled in this browser.', backgroundgColor: '#af272f', fontColor: '#ffffff', fontSize: '14px', okButttonText: 'Accept Cookies', okButttonBackgroundColor: '#ffffff', okButttonTextColor: '#000', okButttonWidth: 'auto', daysToKeepTheAnswer: 183 }; "; } else { echo " "; } ?>