In Using Pivot Table Data for a Chart with a Dual Category Axis, I showed how to make a Chart with a Dual Category Axis using a pivot table to properly arrange the source data. I generally prefer using a regular chart, because pivot charts are pretty inflexible when it comes to formatting. Unfortunately, a regular chart doesn’t know when the pivot table has been resized by a refresh. In Dynamic Chart using Pivot Table and Range Names I showed how to use dynamic ranges to allow a regular chart to update when the pivot table changes. However, that technique only accommodates a change in the number of points in a series, not the number of series in the chart. To allow for changing height and width of a pivot table, you either need to revert to a pivot chart, or use a VBA procedure, which I describe below.

This example begins with the same data range, pivot table, and chart as the previous ones.

I’ve highlighted some sections of the pivot table above to correlate them with pivot table ranges in the VBA object model.

The VBA procedure is not too complicated. It should be run whenever the pivot table has been refreshed. It can be called from a button, or from a Worksheet_Calculate event procedure. Whatever works best in a particular application. Paste this code into a regular code module:

Sub UpdateChartFromPivot()
    Dim rCategories As Range
    Dim rValues As Range
    Dim rSeriesNames As Range
    Dim pt As PivotTable
    Dim cht As Chart
    Dim iSeries As Long
    Dim nSeries As Long

    ' Define the Pivot Table
    'Set pt = ActiveSheet.PivotTables(1)
    Set pt = ActiveSheet.PivotTables("PT_ChartSource")

    ' Define the Ranges
    Set rValues = pt.DataBodyRange
    With pt.RowRange
        Set rCategories = .Offset(1).Resize(.Rows.Count - 1)
    End With
    Set rSeriesNames = pt.ColumnRange.Rows(2)

    ' Define the Chart
    'Set cht = ActiveSheet.ChartObjects(1).Chart
    Set cht = ActiveSheet.ChartObjects("chtPivotData").Chart

    ' How Many Series?
    nSeries = rSeriesNames.Columns.Count

    ' Remove or Add Series until Chart Has Correct Number
    Select Case cht.SeriesCollection.Count - nSeries
        Case Is > 0
            ' too many: remove excess series
            For iSeries = nSeries To cht.SeriesCollection.Count + 1
                cht.SeriesCollection(iSeries).Delete
            Next
        Case Is < 0
            ' too few: add sufficient series
            For iSeries = cht.SeriesCollection.Count + 1 To nSeries
                cht.SeriesCollection.NewSeries
            Next
        Case Else
            ' just right
    End Select

    ' Populate Each Series
    For iSeries = 1 To nSeries
        With cht.SeriesCollection(iSeries)
            .Name = rSeriesNames.Columns(iSeries)
            .Values = rValues.Columns(iSeries)
            .XValues = rCategories
            .Border.LineStyle = xlNone
        End With
    Next

End Sub
 

Let’s add some rows to the pivot table source data:

Now update the pivot table:

And finally, run the VBA procedure to update the chart:

The VBA procedure can be run from the Macros dialog (press Alt+F8 or navigate the menu/ribbon), from a button you’ve placed on the sheet (see Assign a Macro to a Button or Shape and Assign a Macro to an ActiveX Control), from a menu item (see Assign a Macro to a Toolbar or Menu), or from a worksheet event procedure.

In Excel 2003 (and maybe 2002) you can use the Worksheet_PivotTableUpdate event procedure. Select the sheet with the pivot table and chart, right click on the sheet tab, and choose View Code. This opens the code module for the active worksheet. From the left dropdown in the module window, choose Worksheet, and from the right dropdown, choose PivotTableUpdate. The shell for the event procedure is entered into the module for you. Insert the procedure name as shown below, and you’re ready to go:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    UpdateChartFromPivot
End Sub
 

If you’re using an older version of Excel, there is no PivotTableUpdate event. What I do in this case is place a formula in a cell outside the pivot table (say, M1) with a formula like:

=SUM(A1:J20)

where the range A1:J20 includes the pivot table. This formula ensures that a calculation occurs when the pivot table refreshes. Then I use the Worksheet_Calculate event procedure to kick off the chart update procedure. As above, select the sheet with the pivot table and chart, right click on the sheet tab, and choose View Code. This opens the code module for the active worksheet. From the left dropdown in the module window, choose Worksheet, and from the right dropdown, choose Calculate. The shell for the event procedure is entered into the module for you. Insert the procedure name as shown below, and you’re ready to go:

Private Sub Worksheet_Calculate()
    UpdateChartFromPivot
End Sub
 

Fourth in a series

• Chart with a Dual Category Axis
• Using Pivot Table Data for a Chart with a Dual Category Axis
• Dynamic Chart using Pivot Table and Range Names
• Dynamic Chart using Pivot Table and VBA

Possibly Related Posts:

• Dynamic Chart using Pivot Table and Range Names
• Using Pivot Table Data for a Chart with a Dual Category Axis
• Update Regular Chart when Pivot Table Updates
• Chart with a Dual Category Axis
• Extract Chart Data
• VBA to Split Data Range into Multiple Chart Series
• VBA Conditional Formatting of Charts by Series Name
• Extend Range to Add New Series (VBA)
• How To: Assign a Macro to an ActiveX Control
• Regular Charts from Pivot Tables