The default layout for a new pivot table is the Compact layout. You can change the layout to Outline or Tablular Form layout, using VBA. Also, use VBA to determine which layout is currently applied to a pivot table.
For details on making manual changes to the pivot table layout, go to this page: Pivot Table Report Layout
In Outline Form, each Row field is in a separate column, as shown in the pivot table below. There are two Row fields -- Customer and Date. Click here to read more about the Outline Form features.
The following code will change the first pivot table on the active sheet to Outline layout. The code is explained below.
Sub ChangeToOutline() Dim pt As PivotTable On Error Resume Next Set pt = ActiveSheet.PivotTables(1) If Not pt Is Nothing Then pt.RowAxisLayout xlOutlineRow Else MsgBox "No pivot tables on this sheet" End If End Sub
First, the code has the instruction to keep going, even if there is an error.
On Error Resume Next
Next, the code tries to set the pt variable, referring to the first pivot table on the active sheet.
Set pt = ActiveSheet.PivotTables(1)
If there isn't a pivot table on the sheet, Excel won't be able to set that variable, and the pt variable is equal to Nothing
The pt variable is checked, to see if the variable is NOT equal to Nothing.
If Not pt Is Nothing Then
If that is true, then a pivot table was found, and its layout is changed to Outline Report Layout. The RowAxisLayout property controls the pivot table layout.
pt.RowAxisLayout xlOutlineRow
If the pt variable IS Nothing, because no pivot table was found, then a message is shown,
MsgBox "No pivot tables on this sheet"
In Compact Form, all Row fields are in one column, as shown in the pivot table below. Click here to read more about the Compact layout features.
The following code will change the first pivot table on the active sheet to Outline layout.
Sub ChangeToOutline() Dim pt As PivotTable On Error Resume Next Set pt = ActiveSheet.PivotTables(1) If Not pt Is Nothing Then pt.RowAxisLayout xlOutlineRow Else MsgBox "No pivot tables on this sheet" End If End Sub
In Tabular Form, each Row field is in a separate column, as you can see in the pivot table below. Click here to read more about the Tabular Form features.
The following code will change the first pivot table on the active sheet to Tabular layout.
Sub ChangeToTabular() Dim pt As PivotTable On Error Resume Next Set pt = ActiveSheet.PivotTables(1) If Not pt Is Nothing Then pt.RowAxisLayout xlTabularRow Else MsgBox "No pivot tables on this sheet" End If End Sub
The following code will check the first pivot table on the active sheet, and show a message with the name of its first row field, and the current layout.
Sub GetRptLayout() Dim pt As PivotTable Dim strLF As String Set pt = ActiveSheet.PivotTables(1) With pt If .RowFields.Count > 0 Then With .RowFields(1) Select Case .LayoutForm Case 0 strLF = "Tabular" Case 1 If .LayoutCompactRow = True Then strLF = "Compact" Else strLF = "Outline" End If Case Else strLF = "Unknown" End Select End With Else strLF = "No Row Fields" End If MsgBox "First Row Field" & ": " _ & .RowFields(1).Name _ & vbCrLf _ & "Report Layout" & ": " _ & strLF End With End Sub
To see the code, and the data used in the screen shots, you can download the pivotlayoutvba.zip file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the code.
Last updated: May 18, 2021 7:18 PM