Home > Pivot > Macros > Options Pivot Table Option Settings List MacroUse this macro to create a list of pivot table option settings for a selected pivot table. |
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.
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 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
Last updated: March 15, 2023 8:38 PM