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.
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
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.
If you do lots of work with charts in Excel, save time with Jon Peltier's Excel Chart Utility.
Last updated: November 23, 2017 10:36 AM