There has been some discussion lately on the refreshing of the Pivot Tables in Excel Refreshable Reports. I previously had written a Tech Tuesday post about how to get a Pivot Table pulling data from an Excel Refreshable Report. You can view that post here:
With this post brought many different questions from the community. So, I want to clear up a few here.
1. You can include more than one pivot table to be refreshed when the data is refreshed. The way this is done is to put calls to the different pivot tables all in the one set of code you write.
In my example, I have 3 sheets. Sheet3 (Fabrikam, Inc.) is the sheet that contains the refreshable data list. Sheet 1 and 2 both contain pivot tables built off of Sheet3. My pivot table names are PivtoTable1 and PivotTable2. As you can see below, I have just listed both tables to be refreshed within the same set of code. You can do one Pivot table or 20, it doesn’t matter, you just keep adding them to this section as you see below.
2. I don’t recommend copying the code from the previous Tech Tuesday article. When you paste it into the VBA Project, it doesn’t always bring the formatting through properly. If you type it, the formatting should be correct. Things like the quotes aren’t in the correct format. Depending on what the VBA Project picks up for the formatting, you can get many different random errors when trying to save the project or run the refresh in Excel.
3. If you have multiple sheets in your Excel workbook and they don’t all refresh off of the one main sheet that the Excel Refreshable Report creates, you will need to put the code on the sheet they truly read from. For Example, if Sheet1 reads the data from Sheet3, the code to refresh Sheet1 would go on Sheet3. If Sheet2 reads the data from Sheet1, the code needs to be put on Sheet1. This way when Sheet3 is refreshed, it will refresh Sheet1. Once Sheet1 is refreshed, it will trigger Sheet2 to refresh. If you put all the code on Sheet3, Sheet 2 would refresh before Sheet1 is complete and could be incorrect in the data results.
