VBA

A reader named Julie has a regular chart that is linked to a pivot table. When the pivot table is pivoted or updated, it may have different numbers of rows and columns, and the chart must be updated manually. Julie asked whether a procedure could be developed to update the chart automatically.

I’ve done this in a number of projects, so I thought I’d illustrate it with a simple example. Let’s start with this dummy data in a list (I’m using Excel 2003).

Insert a pivot table using this data. Drag the Item field to the Rows area, the Category field to the Columns area, and the Value field to the Data area. Simple enough.

Now create a regular chart from this pivot table, using series in columns, one series per item in the Category field, Category entries (alpha and beta) as series names, and the item names as category (X) axis labels.

[Read more…] about Update Regular Chart when Pivot Table Updates

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 = cht.SeriesCollection.Count To nSeries + 1 Step -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

Update

A follow-up post shows how to Create and Update a Chart Using Only Part of a Pivot Table’s Data.