change grandtotal layout on pivot table in excel 2017

Change grandtotal layout on pivot table in excel 2017 code#

TableName:='PivotTable1', DefaultVersion:=xlPivotTableVersion10 Rename RowLabels and ColumnLabels of Compact Layout TableDestination:=Worksheets('Sheet5').Range('A1'), _ SourceData:=Worksheets('Sheet1').Range('A1:B7'), Version:=xlPivotTableVersion10). (Version10 is what I tested in Excel 2003 although the below table from MSDN shows Version11, but they should have no difference) Nameīelow is an example to create a 2003 version Pivot Table in Excel 2013 ActiveWorkbook.PivotCaches. The DefaultVersion of Excel 2013 is xlPivotTableVersion15 while Excel 2003 is xlPivotTableVersion10. Syntax of PivotCaches and CreatePivotTable PivotCaches.Create( SourceType, SourceData, Version) CreatePivotTable( TableDestination, TableName, ReadData, DefaultVersion) Run-time error ‘5’: Invalid procedure call or argument

Note that version for Pivot Cache and Pivot Table has to be the same, otherwise you will receive the following error.

Change grandtotal layout on pivot table in excel 2017 code#

You can change the layout using VBA Code ActiveSheet.PivotTables('PivotTable1').RowAxisLayout xl OutlineRow ActiveSheet.PivotTables('PivotTable1').RowAxisLayout xl TabularRow ActiveSheet.PivotTables('PivotTable1').RowAxisLayout xl CompactRowĪlternatively, you can define Version and DefaultVersion when you create Pivot Cache and Pivot Table using VBA. There is another new layout called “Outline”, where the layout format is between “Tabular” and “Compact”.īefore continue reading, you should have knowledge in creating Pivot Table using VBA.

Since “Row Labels” refer to both Department and Empl ID as they display in one column, it uses a generic name “Row Labels”. I personally hate this layout because it does not use the actual column name, instead it uses “Row Labels”, “Column Labels”. Pivot Table layout is called “Tabular”, where each grouping is in one column, and the subtotal is at the bottom of each group.īy default, Pivot Table layout is called “Compact”, where groupings are in one column, and subtotal is on top of each group.Įven worse, the column label “Department” and “Empl ID” are gone. We want to show the total summary, group by Department, Empl ID. To illustrate why we want to change Pivot Table Layout, lets see the below example.Īssume that we use the following data to create a Pivot Table.