Skip to content
+1-888-319-3663

EONE BLOG

Tech Tues: Update to Automatically Refreshing Pivot Tables in Excel Refreshable Reports


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:
tech tues box
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.

VBA Code for mutliple pivot tables

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.

1 Comment

  1. Rob on October 22, 2017 at 11:39 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

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

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 Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.