Contextures

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.

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

Download the Sample File

You can download 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.

Excel Chart Utility

If you do lots of work with charts in Excel, save time with Jon Peltier's Excel Chart Utility.

Peltier Tech Charts for Excel 3.0

Related Tutorials

FAQs - Pivot Tables

Excel Slicer Macros

Pivot Table Introduction

Pivot Chart Source Data

30 Excel Functions in 30 Days

 

 

Contextures
Excel news
by email

 

 

 

 

pivot power premium

 

 

 

pivot xtreme

 

pivot power premium

 


Last updated: September 11, 2017 11:14 AM