Home > Pivot > Layout > Macros

Excel Pivot Tables Report Layout VBA

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

Change to Outline 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

How the Code Works

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"

Change to Compact Layout

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

Change to Tabular Layout

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

Determine Current Layout

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

Faster Pivot Table Macros

If you run a pivot table macro, and the code takes a long time to run, try adding code that turns automatic updating on or off for the pivot table.

In the video below, you can see a pivot table refresh macro, and it runs faster with 2 lines of code added.

• Before the pivot table is refreshed, the ManualUpdate setting is turned on (True)
• After the pivot table is refreshed, the ManualUpdate setting is turned off (False)

Here is a short macro example, that refreshes a pivot table when its worksheet is activated. The lines in bold text turn the manual update setting on and off.

Tip: Click this link for more pivot table refresh tips and a macros, to help you with refresh problems.

Private Sub Worksheet_Activate()
Application.EnableEvents = False 
  With Me.PivotTables(1)
    .ManualUpdate = True 
    .RefreshTable
    .ManualUpdate = False
  End With
Application.EnableEvents = True
End Sub

Download the Sample File

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.

More Links

Refresh Pivot Tables

Pivot Table Introduction

Count Duplicates with Pivot Table

Summary Functions