Home > Pivot > Macros > Styles Pivot Table Styles Macros In a complex Excel workbook, these macros can help you document the pivot table styles information, for future reference or troubleshooting. Use these pivot table styles macros to list all pivot tables, with their style info, or list custom and built-in styles. Also, use macros to list default styles for the active workbook, or change them. Download the sample workbook with macros.

 

List All Pivot Tables

Use this macro to create a list of all the pivot tables in the active workbook, with information on the pivot table style applied to each one. This macro shows the sheet, pivot table name, pivot table address, style name, BuiltIn (False if Custom), and the header, Row Subheading 1, and inside border colours.

This macro is in the sample workbook that you can download. Or, to use the macro in your own workbook, copy it to a regular code module.

The macro adds a sheet to the workbook, with the a list of the pivot tables and their style settings..

Macro to List All Pivot Tables with Style Info

Sub PTsAll_ListStyles()
Dim wb As Workbook
Dim wsL As Worksheet
Dim ws As Worksheet
Dim pt As PivotTable
Dim stl As TableStyle
Dim lClrH As Long
Dim lClrR As Long
Dim lClrB As Long
Dim myRow As Long
On Error Resume Next

Set wb = ActiveWorkbook
Set wsL = Sheets.Add

With wsL
  .Range(Cells(1, 1), Cells(1, 8)).Value _
    = Array("Sheet", "PT Name", "PT Address", _
        "Style", "BuiltIn", "Header", _
          "Row SH1", "Borders")
End With

myRow = 2

For Each ws In wb.Worksheets
  For Each pt In ws.PivotTables
    Set stl = pt.TableStyle2
    With wsL
      .Cells(myRow, 1).Value = ws.Name
      .Cells(myRow, 2).Value = pt.Name
      .Cells(myRow, 3).Value = _
        pt.TableRange2.Address
      If Not stl Is Nothing Then
        .Cells(myRow, 4).Value = stl.NameLocal
        .Cells(myRow, 5).Value = stl.BuiltIn
        
        lClrH = stl.TableStyleElements _
            .Item(xlHeaderRow).Interior.Color
        If lClrH = 0 Then
          .Cells(myRow, 6).Value = "•"
        Else
          .Cells(myRow, 6).Interior.Color = lClrH
        End If
        
        lClrR = stl.TableStyleElements _
            .Item(xlRowSubheading1).Interior.Color
        If lClrR = 0 Then
          .Cells(myRow, 7).Value = "•"
        Else
          .Cells(myRow, 7).Interior.Color = lClrR
        End If
    
        lClrB = stl.TableStyleElements _
            .Item(xlWholeTable) _
              .Borders(xlInsideHorizontal).Color
        If lClrB = 0 Then
          .Cells(myRow, 8).Value = "•"
        Else
          .Cells(myRow, 8).Interior.Color = lClrB
        End If
      
      Else
        .Cells(myRow, 4).Value = "None"
      End If
      myRow = myRow + 1
    End With
    Set stl = Nothing
  Next pt
Next ws

With wsL
  .Range("A1:H1").Font.Bold = True
  .Range("J1").Value = "• = Black"
  .Columns("A:J").EntireColumn.AutoFit
  .Columns("E:H").HorizontalAlignment = xlCenter
  .Columns(9).ColumnWidth = 3.57
  .Range("A1").Select
End With

End Sub

List All Pivot Table Styles

Use this macro to create a list of all the pivot table styles in the active workbook. This macro shows the style name and number, BuiltIn (False for Custom styles), and the header and inside border colours.

This macro is in the sample workbook that you can download. Or, to use the macro in your own workbook, copy it to a regular code module.

The macro adds a sheet to the workbook, with a list of the pivot table style settings.

Macro to List All Pivot Table Styles

Sub StylesPTListALL()
Dim wb As Workbook
Dim lStyle As Long
Dim stl As TableStyle
Dim ws As Worksheet
Dim myRow As Long
Dim lClrH As Long
Dim lClrB As Long
Set wb = ActiveWorkbook
Set ws = Sheets.Add
On Error Resume Next

With ws
  .Range(Cells(1, 1), Cells(1, 5)).Value _
    = Array("Style", "Name", "BuiltIn", _
      "Header", "Borders")
End With

myRow = 2

For lStyle = 1 To wb.TableStyles.Count
  Set stl = wb.TableStyles(lStyle)
  If stl.ShowAsAvailablePivotTableStyle = True Then
    ws.Cells(myRow, 1).Value = lStyle
    ws.Cells(myRow, 2).Value = stl.NameLocal
    ws.Cells(myRow, 3).Value = stl.BuiltIn
    
    lClrH = stl.TableStyleElements _
        .Item(xlHeaderRow).Interior.Color
    If lClrH = 0 Then
      ws.Cells(myRow, 4).Value = "•"
    Else
      ws.Cells(myRow, 4).Interior.Color = lClrH
    End If
    
    lClrB = stl.TableStyleElements _
        .Item(xlWholeTable) _
          .Borders(xlInsideHorizontal).Color
    If lClrB = 0 Then
      ws.Cells(myRow, 5).Value = "•"
    Else
      ws.Cells(myRow, 5).Interior.Color = lClrB
    End If
    
    myRow = myRow + 1
  End If
Next lStyle

With ws
  .Range("A1:E1").Font.Bold = True
  .Range("G1").Value = "• = Black"
  .Columns("A:G").EntireColumn.AutoFit
  .Columns("C:E").HorizontalAlignment = xlCenter
  .Columns(6).ColumnWidth = 3.57
  .Range("A1").Select
End With

End Sub

List Custom Pivot Table Styles

Use this macro to create a list of all the custom pivot table styles in the active workbook. This macro shows two of the pivot table style settings, and you could modify the macro , to list more of the setttings.

This macro is in the sample file that you can download. Or, to use the macro in your own workbook, copy it to a regular code module.

The macro adds a sheet to the workbook, with the a list of the pivot tables and their style settings..

Macro to List All Pivot Tables

Sub StylesPTListCustom()
Dim wb As Workbook
Dim stl As TableStyle
Dim ws As Worksheet
Dim CountCS As Long
Dim lClrH As Long
Dim lClrB As Long
Dim myRow As Long
Set wb = ActiveWorkbook

For Each stl In wb.TableStyles
  If stl.ShowAsAvailablePivotTableStyle _
      And stl.BuiltIn = False Then
      CountCS = CountCS + 1
  End If
Next stl

If CountCS = 0 Then
  MsgBox "No custom pivot table styles"
  Exit Sub
End If

Set ws = Sheets.Add
With ws
  .Range(Cells(1, 1), Cells(1, 3)).Value _
    = Array("Custom Style", "Header", "Borders")
End With

myRow = 2

For Each stl In wb.TableStyles
  If stl.ShowAsAvailablePivotTableStyle _
      And stl.BuiltIn = False Then
    ws.Cells(myRow, 1).Value = stl.Name
    
    lClrH = stl.TableStyleElements _
        .Item(xlHeaderRow).Interior.Color
    If lClrH = 0 Then
      ws.Cells(myRow, 2).Value = "•"
    Else
      ws.Cells(myRow, 2).Interior.Color = lClrH
    End If
    
    lClrB = stl.TableStyleElements _
        .Item(xlWholeTable) _
          .Borders(xlInsideHorizontal).Color
    If lClrB = 0 Then
      ws.Cells(myRow, 3).Value = "•"
    Else
      ws.Cells(myRow, 3).Interior.Color = lClrB
    End If
    
    myRow = myRow + 1
  End If
Next stl

With ws
  .Range("A1:C1").Font.Bold = True
  .Range("E1").Value = "• = Black"
  .Columns("A:C").EntireColumn.AutoFit
  .Columns(3).HorizontalAlignment = xlCenter
  .Columns(4).ColumnWidth = 3.57
  .Range("A1").Select
End With

End Sub

List Workbook Default Styles

When you create a pivot table, a default PivotTable Style is automatically applied. To see the names of the default pivot table style, and the default table style, run the following macro.

This macro is in the sample file that you can download. Or, to use the macro in your own workbook, copy it to a regular code module.

The macro adds a sheet to the workbook, with the a list of the default styles.

Macro to List Default Styles in Workbook

Sub StylesWBDefaultList()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ActiveWorkbook
Set ws = Sheets.Add
With ws
  .Range(Cells(1, 1), Cells(1, 2)).Value _
    = Array("Default PT Style", _
      wb.DefaultPivotTableStyle)
  .Range(Cells(2, 1), Cells(2, 2)).Value _
    = Array("Default Table Style", _
      wb.DefaultTableStyle)
  .Columns("A:B").EntireColumn.AutoFit
  .Range("A1").Select
End With

End Sub

Set the Default Style

If you create a custom style, or apply one of the built-in pivot table styles, you might want to use that as the default pivot table style for the workbook. Select a cell in the formatted pivot table, then run this macro, to make that the default style for the workbook.

The macro changes the default pivot table style for the workbook, based on the pivot table style for the selected pivot table.

This macro is in the sample file that you can download. Or, to use the macro in your own workbook, copy it to a regular code module.

Macro to Set the Default Style in Workbook

To run the macro,

• Select a cell in the pivot table, then on the Ribbon's View tab, click Macros
• Select the macro named StylesWBDefaultSet, and click Run

If the pivot table style is the current default style, a message will tell you that.

Sub StylesWBDefaultSet()
Dim wb As Workbook
Dim pt As PivotTable
Dim StyleOld As String
Dim StyleNew As String

Set wb = ActiveWorkbook
On Error Resume Next
Set pt = ActiveCell.PivotTable

If pt Is Nothing Then
MsgBox "Please select a pivot table cell" _
  & vbCrLf _
  & "and try again"
Exit Sub
End If

StyleOld = wb.DefaultPivotTableStyle
wb.DefaultPivotTableStyle = pt.TableStyle2
StyleNew = wb.DefaultPivotTableStyle

If StyleOld = StyleNew Then
  MsgBox "The default PivotTable Style " _
  & " was NOT changed " _
  & vbCrLf _
  & vbCrLf _
  & "The selected PivotTable uses " _
  & " the current default style:" _
 & vbCrLf _
  & StyleNew
Else
  MsgBox "The default PivotTable " _
  & " Style was changed " _
  & vbCrLf _
  & "from: " & StyleOld _
  & vbCrLf _
  & "to:     " & StyleNew
End If

End Sub

Download the Sample File

To experiment with pivot table styles macros, download the Styles Macros sample file. The zipped file is in xlsm format, and contains macros to work with the PivotTable Styles.   ▲TOP

More Pivot Table Resources

FAQs - Pivot Tables

Pivot Table Introduction

Pivot Table Formatting

Classic Pivot Table Format

Copy Pivot Table Formatting