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. 
5. Double click on the sheet with your data set.  This will open the code window for you to enter in the code.  
   
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:
   

7. Save the code by going to File – Save.