Change Pivot Table Layout using VBA

Change Pivot Table Layout, why?

To illustrate why we want to change Pivot Table Layout, lets see the below example.

Assume that we use the following data to create a Pivot Table. We want to show the total summary, group by Department, Empl ID.

pivot_table_layout_001

Excel 2003 – Tabular layout

Pivot Table layout is called “Tabular”, where each grouping is in one column, and the subtotal is at the bottom of each group.

pivot_table_layout_002

Excel 2007 and onwards – Compact layout

By default, Pivot Table layout is called “Compact”, where groupings are in one column, and subtotal is on top of each group.

Even worse, the column label “Department” and “Empl ID” are gone.

pivot_table_layout_003

I personally hate this layout because it does not use the actual column name, instead it uses “Row Labels”, “Column Labels”.

Since “Row Labels” refer to both Department and Empl ID as they display in one column, it uses a generic name “Row Labels”.

Excel 2007 and onwards – Outline layout

There is another new layout called “Outline”, where the layout format is between “Tabular” and “Compact”.

pivot_table_layout_004

Change Pivot Table Layout using VBA

Before continue reading, you should have knowledge in creating Pivot Table using VBA. If not, read the below article first

Create Pivot Table using Excel VBA

You can change the layout using VBA Code

ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlOutlineRow
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlCompactRow

Alternatively, you can define  Version and DefaultVersion when you create Pivot Cache and Pivot Table using VBA. Note that version for Pivot Cache and Pivot Table has to be the same, otherwise you will receive the following error.

Run-time error ‘5’: Invalid procedure call or argument

run_time_error5

Syntax of PivotCaches and CreatePivotTable

PivotCaches.Create(SourceType, SourceData, Version)
CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)

The DefaultVersion of Excel 2013 is xlPivotTableVersion15 while Excel 2003 is xlPivotTableVersion10. (Version10 is what I tested in Excel 2003 although the below table from MSDN shows Version11, but they should have no difference)

Name Value Description
xlPivotTableVersion2000 0 Excel 2000
xlPivotTableVersion10 1 Excel 2002
xlPivotTableVersion11 2 Excel 2003
xlPivotTableVersion12 3 Excel 2007
xlPivotTableVersion14 4 Excel 2010
xlPivotTableVersion15 5 Excel 2013
xlPivotTableVersionCurrent -1 Provided only for backward compatibility

Below is an example to create  a 2003 version Pivot Table in Excel 2013

    ActiveWorkbook.PivotCaches. _
        Create( _
        SourceType:=xlDatabase, _
        SourceData:=Worksheets("Sheet1").Range("A1:B7"), Version:=xlPivotTableVersion10). _
            CreatePivotTable _
            TableDestination:=Worksheets("Sheet5").Range("A1"), _
            TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

Rename RowLabels and ColumnLabels of Compact Layout

You can change any label in the Pivot Table.

To change RowLabels and Column Labels

ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "NewRowName"
ActiveSheet.PivotTables("PivotTable1").CompactLayoutColumnHeader = "NewColumnName"

pivot_table_layout_005

To change “Grand Total”

ActiveSheet.PivotTables("PivotTable1").GrandTotalName = "NewGrandTotal"

VBA_pivottable_006

To change name of Pivot Items

Any other names you see in the Pivot Table are called “Pivot Items” (such as Department name, Empl ID), the name can be changed through Caption Property.

Below example shows how to change the Pivot Items “Sum of Salary” to “NewSumOfSalary”.

ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems("Sum of Salary").Caption = "NewSumOfSalary"

pivot_table_layout_007

What’s next?

Now you have created a Pivot Table, and changed the layout you want. The next step is to play around with Filters in VBA. You are recommended to read the article.

Excel VBA multiple criteria for Label Filter of Pivot Table

Outbound References

http://msdn.microsoft.com/en-us/library/office/ff837609%28v=office.15%29.aspx

https://www.youtube.com/watch?v=M04rnVoD5mc

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *