Pivot Chart VBA

An Excel pivot chart layout is tied to the layout of the pivot table on which it is based. If you change one, the other will also change. Use this macro to list all fields currently in the pivot chart layout

List Fields in Pivot Chart Layout With VBA

This macro will add a sheet to the workbook, and list all the pivot charts, and the pivot fields in their layout

To use this macro, copy the code to a regular code module. Next, MAKE A BACKUP of your file, before running the macro -- most Excel macros cannot be undone

Then, to run the macro, click the View tab on the Ribbon, and click the Macros command. In the list of macros, select ListFieldNames, and then click Run.

Sub ListFieldNames()
Dim wsNew As Worksheet
Dim lRow As Long
Dim pf As PivotField
Dim pc As ChartObject
Dim ws As Worksheet
Dim strLoc As String
Set ws = ActiveSheet
Set wsNew = Worksheets.Add
lRow = 2
'On Error Resume Next
With wsNew
  .Range(.Cells(1, 1), .Cells(1, 4)).Value _
    = Array("Sheet", "Chart", _
        "Field", "Location")
  .Rows("1:1").Font.Bold = True
End With

For Each ws In ActiveWorkbook.Worksheets
  For Each pc In ws.ChartObjects
    With pc.Chart
      If Not .PivotLayout Is Nothing Then
        For Each pf In _
          If pf.ShowingInAxis = True Then
          Select Case pf.Orientation
            Case 1: strLoc = "Row"
            Case 2: strLoc = "Column"
            Case 3: strLoc = "Filter"
            Case Else: strLoc = "Values"
          End Select
          wsNew.Range(wsNew.Cells(lRow, 1), _
            wsNew.Cells(lRow, 4)).Value _
            = Array(ws.Name, pc.Name, _
              pf.Caption, strLoc)
          lRow = lRow + 1
          End If
        Next pf
      End If
    End With
  Next pc
Next ws

End Sub

Get the Sample File

You can get a copy of the Pivot Chart Layout sample file, to test the pivot chart field list macro. The zipped file is in xlsm (macro-enabled) format. After you unzip the file and open it, enable macros, so you can use the macros.

Related Tutorials

FAQs - Pivot Tables

Pivot Chart Compare Years

Pivot Chart Number Formatting

Excel Slicer Macros

Pivot Table Introduction

Pivot Chart Source Data

Get weekly Excel tips from Debra


Last updated: June 15, 2021 3:45 PM