Home > Pivot > Macros > Options Pivot Table Option Settings List Macro Use this macro to create a list of pivot table option settings for a selected pivot table.

Pivot Table Options

There are many settings available in the Pivot Table Options dialog box, where you can control the appearance and behaviour of the pivot table. To access this dialog box, right-click on any cell in the pivot table, and click PivotTable Options.

Pivot Table Options List - Short

This macro creates a short list of pivot table options, and shows their current settings. The macro inserts a new sheet in the workbook, lists the option settings, and formats the list as a named table.

NOTE: Select a pivot table cell before running this macro.

Sub OptionSet_Short()
'short list of option settings
'select a pivot table cell
' before running this macro
Dim wsList As Worksheet
Dim ws As Worksheet
Dim pt As PivotTable
Dim OptList As ListObject

Dim i As Long   'row number
Dim OptID As Long
Dim strTab As String
'Dim strSF As String
'Dim strMI As String

On Error Resume Next
Set ws = ActiveSheet
Set pt = ActiveCell.PivotTable

If pt Is Nothing Then
  MsgBox "Please select a pivot table cell"
  GoTo exitHandler
End If
Application.EnableEvents = False

Set wsList = Worksheets.Add
i = 3   'leave rows for sheet heading
OptID = 1

With wsList
  'Table Headings
  .Cells(i, 1).Value = "ID"
  .Cells(i, 2).Value = "Tab Name"
  .Cells(i, 3).Value = "Option"
  .Cells(i, 4).Value = "Setting"
  i = i + 1
  
  '----------------
  'Tab 1
  strTab = "Layout & Format"
  
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Autofit column widths on update"
  .Cells(i, 4).Value = pt.HasAutoFormat
  i = i + 1
  OptID = 1 + 1
  
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Preserve cell formatting on update"
  .Cells(i, 4).Value = pt.PreserveFormatting
  i = i + 1
  OptID = 1 + 1
  
  '----------------
  'Tab 2
  strTab = "Totals & Filters"
  
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Show grand totals for rows"
  .Cells(i, 4).Value = pt.RowGrand
  i = i + 1
  OptID = 1 + 1
  
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Show grand totals for columns"
  .Cells(i, 4).Value = pt.ColumnGrand
  i = i + 1
  OptID = 1 + 1
  
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Allow multiple filters per field"
  .Cells(i, 4).Value = pt.AllowMultipleFilters
  i = i + 1
  OptID = 1 + 1
  
  '----------------
  'Tab 3
  strTab = "Display"
  
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Show expand/collapse buttons"
  .Cells(i, 4).Value = pt.ShowDrillIndicators
  i = i + 1
  OptID = 1 + 1
   
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Show contextual tooltips"
  .Cells(i, 4).Value = pt.DisplayContextTooltips
  i = i + 1
  OptID = 1 + 1
   
  '----------------
  'Tab 4
  strTab = "Printing"
  
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Set print titles"
  .Cells(i, 4).Value = pt.PrintTitles
  i = i + 1
  OptID = 1 + 1
  
  '----------------
  'Tab 5
  strTab = "Data"
    
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Save source data with file"
  .Cells(i, 4).Value = pt.SaveData
  i = i + 1
  OptID = 1 + 1
  
  .Cells(i, 1).Value = OptID
  .Cells(i, 2).Value = strTab
  .Cells(i, 3).Value = "Refresh data when opening the file"
  .Cells(i, 4).Value = pt.PivotCache.RefreshOnFileOpen
  i = i + 1
  OptID = 1 + 1
    
  '----------------

  'format the options list as table
   Set OptList = .ListObjects.Add(xlSrcRange, _
      .Range("A3").CurrentRegion, , xlYes)
    'OptList.TableStyle = "TableStyleLight8"
    .Columns("A:D").EntireColumn.AutoFit
  
  'Sheet Heading
  .Cells(1, 1).Value = "PIVOT TABLE OPTIONS - " & pt.Name
  .Cells(1, 1).Font.Bold = True
  i = i + 2

End With

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

Download the Workbook

Download the sample workbook for the PivotTable Options examples on this page. The zipped file is in xlsb format, and contains the macros shown on this page, along with a sample pivot table for testing

More Tutorials

Pivot Table Layout VBA

FAQs - Pivot Tables

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table