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. |
Pivot Chart Macro CodeTo 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 |
Run the Pivot Chart MacroBefore 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:
|
Get the Sample FileYou 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 |
Last updated: November 7, 2022 3:56 PM