Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Tuesday, December 1, 2015
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.
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.
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.
- 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.
- Expand the Developer Tab in Excel.
- Select Visual Basic to open the VBA Project.
- Double click on the sheet with your data set. This will open the code window for you to enter in the code.
- 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.RefreshEnd 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:
- 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.
This is a genius technique.
Awesomeness!!! thx!
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?
I have the exact same problem, keep getting the error “run-time error ‘9’ subscript out of range”
Double quotation mark must to be the vertical lines. Copy-pasted from web may have inserted the wrong ones. Retype them with you keyboard.
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.
Thank you so much! What would be the correct formula if you want to add multiple pivot tables to be refreshed?
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
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
I have the same problem.
Same problem as Jade & Healer. Clicked Refresh & nothing happens.
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?
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.
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
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.
Thanks Garrett – changing the quotes solved my debugging issue!
YES! That worked. Thanks, Garrett
Worked for me too. Thanks so much.
Thank you for that fix.
Changing the quotes worked for me!
Thanks Garrett
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..
Brilliant, thanks.
Garretts solution worked …. thanks
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.
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,
Thanks for this information, works great!
try this
Dim PT as pivot tables
For Each PT In WS3.PivotTables
PT.PivotCache.Refresh
Next PT
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
If I have one database but more than one pivot table, how can i use it?
Is there a way to update the pivot table when the main data sheet expands but columns and rows both?
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
Bit slow but works brill, thanks
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?
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!
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
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?
Garrett you little Genius. Thanks
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
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
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.
I\\\’ve inputted this code but nothing is happening. I don\\\’t even get an error. Is there something obvious I\\\’ve done wrong?
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?
I have the same exact issue as Rachel – hand typed everything and the error 9/subscript error still persists.
Me too!
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…
please ignore the black slashes \\ – I’ve no idea why they’re in there!
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!
Beautiful. Thanks!
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 🙂 🙂
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.
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
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?
Brilliant – just what I needed! 🙂
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
Thanks!
Thanks so much, Tim – this should be at the top.
Thank you so much, It\’s really help me a lot
The VBA code works great unless the worksheet with the pivot table is protected. Is there a way around that?
You are awesome!, wish I would have known about this a long time ago
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
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.
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