|
Home > Pivot > Layout > Pivot Chart Pivot Chart VBAAn 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. |

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:

To use this macro, copy the code below to a regular code module in the Visual Basic Editor in your Excel workbook.
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
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:
Last updated: September 18, 2024 9:45 AM