Sometimes you just need to write macros for pivot tables. It’s a fact of life.

Maybe your pivot table is dynamically populated with data and you need to make some custom changes once the data is in the table, but you can only do that after the file is opened in Excel and the pivot table refreshes. Perhaps your plagued with the error that happens when you open a file with a pivot table directly from Internet Explorer. Whatever your reason, you need to write some VBA for pivot tables.

Here’s my super short list of tips for working with pivot tables in VBA:

#1 – Record a macro of yourself to get started

If you know what you want to do, but you’re not sure what the VBA code should be, record a macro of yourself doing the desired action. Not only will this give you a hint about where to start in your VBA, but you’ll also verify the exact method calls for your version of Excel and your pivot table version. All of my pivot table macros start with me performing operations in Excel and then generalizing the VBA from there.

#2 – Base your VBA on pivot fields

Pivot fields come from the column names in the data source for your pivot table. Even as the data is changing, the pivot fields remain constant because the construction and layout of the pivot table depends on the pivot fields. The fact that they remain constant can be very useful when writing VBA.

If you create macros that are based on particular pivot table items, you can’t be sure that those values will be there when the workbook is populated. For example, you want to make sure that all the groups for a particular row label are collapsed when the user first encounters the pivot table. You can manually collapse each of these groups. Problem: you create a macro that collapses groups “A”, “B”, “C”, etc separately. What if group “A” isn’t in your data set when the report is generated? Excel will throw an errror.

You can take the chance that group “A” may or may not appear in your data set, or you can create a macro that focuses on the pivot field that group “A” belongs to, rather than the individual entries.

ActiveSheet.PivotTables("PivotTable1").PivotFields("PivotField1").ShowDetail = False

This will collapse all the groups within ‘PivotField1’.

In short, avoid making macros that are dependent on specific data, because you can’t be sure that the VBA will execute without error.

#3 – VBA for pivot tables in Excel 2003 is different than in Excel 2007/2010

Excel 2003 had our best friend, the Pivot Table Wizard. Everything for the pivot table was done through a wizard menu, so the VBA in Excel 2003 follows suite. Starting in Excel 2007, Excel reworked how users interacted with pivot tables, and the Pivot Table Wizard was banished for eternity. Thus, VBA in Excel 2007/2010 is drastically different.

If you have end-users who are going to be opening a report with pivot table VBA in Excel 2003, 2007, and 2010, you’ll want to detect what version of Excel is being opened, then execute the 2003 or 2007/2010 based on this.

To determine what version of Excel the VBA is executing in, check Application.Version. This will return a string representing the version of Excel (e.g. 10.0, 12.0, 14.0 etc.)

#4 – Pivot tables have versions too

Just as there are differences between XLS and XLSX files in terms of what features can be supported, there are differences between pivot tables created in XLS files vs those created in XLSX files. What’s more, there are also differences between pivot tables created in Excel 2007 and 2010. It’s important to note what version of pivot table you’re targeting.

Luckily you can check the PivotTable.Version property to see which of the versions your pivot table is.

So there you have it – my cheat sheet for creating VBA for pivot tables.

Follow

Related posts: