Automating Pivot Tables with VBA
Pivot Tables are very easy to set up and exceptionally useful once they have the elements you wish to summarise inside them. It is mostly a drag and drop technology that vastly simplifies calculations of large data sets. They are powerful Excel tools that everyone should know about. The update of data inside a pivot table is a most important task. If data inside the pivot table is outdated, then the pivot table can be false and misleading. Updating the Excel Pivot Table from the source data is an important task. Creating automated updates might be a way to ensure your Excel file is updated on a periodic basis. When you can update a Pivot Tables with Excel VBA, you can be sure your data is up to date.
Refreshing Pivot Table
Using the RefreshTable method for an Excel pivot table is possibly the simplest way to update a pivot table automatically. The name of the worksheet code name and the pivot table is important. You could use the worksheet name but we don’t do that as if someone changes the name of the sheet the code will fail without it also being changed. Using the worksheet code name circumvents this problem FOREVER. The worksheet code name in the following example is Sheet1 and the pivot table name is PivotTable1.
Sheet1.PivotTables("PivotTable1").Refreshtable
Once again be aware of the sheet code name and the pivot table name.
Click on the pivot table, in the top left under the Analyze menu you will find the pivot table name.
Pivot Tables and Pivot Caches - What is the difference?
A pivot table is made up of a number of elements, the raw data that feeds the pivot table, the pivot cache, data stored in memory of the underlying data set and the physical pivot table itself which you drag and drop fields in to show your data.
Pivot Caches may be a new concept, they store the raw data to be used in a Pivot Table, and one pivot cache can be used for many different different pivot tables. When we refresh one Pivot Table that has the same cache, then the refresh affects all pivot tables sharing the same cache, so effectively all pivot tables are refreshed. An important point to note is that to update specific pivot tables with VBA, it is required that the pivot tables need to have separate pivot caches. This can be problematic if your data set is large as a duplicate data set would be required. This is often not desirable.
Refresh the Pivot Cache
The following will refresh the pivot cache for all the pivot tables that are sourced from the cache. Remembe the worksheet code name and pivot table name are important.
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
This is a very efficient way to update one pivot table and have a range of pivot tables update also, as you only need to know the name of one pivot table (PivotTable1), and the coding will run efficiently to update all pivot tables with the same pivot cache.
Updating All Pivot Tables in a Workbook
To update all of the pivot tables in a workbook regardless of the pivot cache shared on not the following will update everything in a seamless batch. It is most efficient.
ThisWorkbook.RefreshAllThe above are some of the methods for updating pivot tables with code. The following workbook contains some examples that you might find useful when implementing your own automated pivot tables.
Updating All Pivot Tables on a Single Sheet
Sometimes you may not wish to change all the pivot tables in the whole workbook, just a single worksheet that contains a number of pivot tables. In this case you will want to create a loop through all the pivot tables and refresh them.
The following is some VBA code that may assist in this task. This has saved me on more than one occasion. Be sure you include a range with the Date in it.
Sub PivFilter()
Dim piv As PivotTable
piv.PivotFields("Date").CurrentPage = Range("Date")
The following file might help to crystallise some of the concepts discussed above.