Home > Pivot > Layout > Pivot Chart

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 new sheet to the Excel workbook, and list all the pivot charts, and the pivot fields in their layout

There are four columns in the list that the macro creates:

1. Sheet - worksheet name where chart is located
2. Chart - name of chart object on worksheet
3. Field - pivot field name in chart layout
4. Location - field's location in related pivottable layout:
   • row field, column field, filter field or data field (Values)

Pivot Chart Macro Code

To use this macro, copy the code below to a regular code module in the Visual Basic Editor in your Excel workbook.

• First, the VBA code adds a new worksheet, with a default name, such as "Sheet1"
• Next, the macro adds the headings in first row on new sheet
• After that, the macro goes to each sheet in the active workbook.
• For each pivot chart on the sheet, it gets the chart details
• It adds those chart details to the Chart List sheet, starting in row 2, and going down one row for each additional pivot chart

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 _
          .PivotLayout.PivotFields
          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

Run the Pivot Chart Macro

Before you run a macro in Excel, it's good practice to MAKE A BACKUP of your file.

Most Excel macros cannot be undone, and you don't want to damage an important file that's working well!

After you make a file backup, follow these steps, to run the Pivot Chart macro:

• First, click the View tab on the Excel Ribbon
• At the right end of the View tab, click the Macros command.
• In the list of macros, click the name ListFieldNames, to select it
• Then, click the Run button.

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