List All Pivot Table Styles Macro

List Your Pivot Table Styles http://contexturesblog.com/

When you create a pivot table, a default PivotTable Style is automatically applied. You can change to a different style, and you can even create custom pivot table styles. To help you keep track of the styles that you have, here’s a List All Pivot Table Styles macro.

Pivot Table Custom Styles

If you’re not sure how to create your own custom pivot table style, this short video shows the steps. Also, there are step-by-step written instructions on my Contextures site, on the Pivot Table Formatting page.

Default and Custom Pivot Table Styles

There are  about 85 built-in pivot table styles in my version of Excel – there might be more than that in your version, or fewer styles.

If you create custom pivot table styles, they’ll be added to that list too. The custom styles also appear at the top of the style gallery.

List All the Pivot Table Styles

To get a list of all the pivot table styles in the active workbook, use the macro that’s shown below. The macro adds a sheet to the workbook, with a list of the pivot table style settings.

The list shows the style name and number, whether it’s built in or custom, the header colour,  and inside border colour.

NOTE: Lots of black is used for the style formatting, so the macro shows a black dot, instead of filling the cell with black. I find that easier to read, and it also saves on printer toner, if you want to print the list!

Macro to List All Pivot Table Styles

This macro to list all Pivot Table styles is in the sample workbook that you can download. There are four other macros too, to list and set the styles.

Or, to use this macro in your own workbook, copy the code below to a regular code module. Then, add a worksheet button to run the macro, or run it from the Macros command on the Excel Ribbon’s View tab.

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

Get the Sample Workbook

To get the sample workbook with pivot tables, custom styles, and more macros, go to the Pivot Table Styles Macros page on my Contextures website.

The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the workbook, if you want to test the macros.

More Pivot Table Resources

Pivot Table Formatting

Classic Pivot Table Format

Copy Pivot Table Formatting

__________________

List Your Pivot Table Styles http://contexturesblog.com/

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.