Pivot Table Options List Macro

When you’re setting up a pivot table, you can use the Ribbon commands to change its appearance, and the source data, and several other settings. There’s another place where you can control the pivot table’s behaviour too – in the Pivot Table Options. See how to access that dialog box, and run this pivot table options list macro to see the current settings, for a few of the key options.

Open PivotTable Options

To see the pivot table options, and change the settings, follow these steps:

• Right-click on any cell in the pivot table
• Click on PivotTable Options

Pivot Table Options Dialog Box

The PivotTable Options dialog box opens, with the PivotTable name at the top, and six tabs below that.

• Layout & Formatting
• Totals & Filters
• Display
• Printing
• Data
• Alt Text

Short List of Key Pivot Table Options

The following macro will add a new sheet to your workbook, with a short list of key pivot table option settings, for the currently selected pivot table. The name of the pivot table is added to the worksheet heading in cell A1.

Pivot Table Options List Macro Code

Copy the following code, and add it to a regular code module in your workbook. Then, select any cell in a pivot table, and run the macro to create a list of key pivot table options, and their current settings.

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

Get the Sample File

To download the sample file with this macro, and a pivot table for testing, go to the Pivot Table Options Macros page on my Contextures website.

The zipped file is in xlsb format, and contains the macro shown above.

________________________

Pivot Table Options List Macro

________________________