Update Regular Chart when Pivot Table Updates

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.

In order to determine which ranges to use for the chart’s source data, it helps to understand what parts of a pivot table are designated as certain ranges. This color coded pivot table illustrates the RowRange, the ColumnRange, the DataBodyRange, and the DataLabelRange. Several of these will be used in the procedure below.

I have set up the macro to put series in columns unless the pivot table DataBodyRange has only one row, in which case there is only one series, and it is plotted by row.

Here is the VBA procedure. Implement it using How To: Use Someone Else’s Macro.

Sub UpdateChartFromPivotTable()
  Dim rValues As Range
  Dim rCats As Range
  Dim rTitles As Range
  Dim cht As Chart
  Dim pt As PivotTable
  Dim chttype As XlChartType
  Dim iSrsIx As Long
  Dim iSrsCt As Long
  Dim iCols As Long
  Dim iRows As Long

  Set cht = ActiveSheet.ChartObjects(1).Chart
  Set pt = ActiveSheet.PivotTables(1)

  Set rValues = pt.DataBodyRange
  On Error Resume Next
  iRows = pt.RowRange.Rows.Count
  iCols = pt.ColumnRange.Columns.Count
  On Error GoTo 0

  If iRows > 0 Then
    Set rCats = Intersect(rValues.EntireRow, pt.RowRange.EntireColumn)
    If iCols > 0 Then
      Set rTitles = Intersect(rValues.EntireColumn, pt.ColumnRange.EntireRow)
      Set rTitles = rTitles.Offset(1).Resize(rTitles.Rows.Count - 1)
    End If
    iSrsCt = rValues.Columns.Count
  ElseIf iCols > 0 Then
    Set rCats = Intersect(rValues.EntireColumn, pt.ColumnRange.EntireRow)
    Set rCats = rCats.Offset(1).Resize(rCats.Rows.Count - 1)
    iSrsCt = rValues.Rows.Count
  Else
    MsgBox "Pivot Table has no fields in Rows area or Columns area.  ", _
        vbCritical + vbOKOnly
    GoTo ExitSub
  End If

  If cht.SeriesCollection.Count > iSrsCt Then
    For iSrsIx = cht.SeriesCollection.Count To iSrsCt + 1 Step -1
      With cht.SeriesCollection(iSrsIx)
        chttype = .ChartType
        .ChartType = xlColumnClustered
        .Delete
      End With
    Next
  ElseIf cht.SeriesCollection.Count < iSrsCt Then
    For iSrsIx = cht.SeriesCollection.Count + 1 To iSrsCt
      cht.SeriesCollection.NewSeries
    Next
  End If

  For iSrsIx = 1 To cht.SeriesCollection.Count
    With cht.SeriesCollection(iSrsIx)
      chttype = .ChartType
      .ChartType = xlColumnClustered
      .XValues = rCats
      If iRows > 0 Then
        .Values = rValues.Columns(iSrsIx)
        If iCols > 0 Then
          .Name = rTitles.Columns(iSrsIx)
        Else
          .Name = "Data"
        End If
      Else
        .Values = rValues.Rows(iSrsIx)
        .Name = "Data"
      End If
      .ChartType = chttype
    End With
  Next

ExitSub:

End Sub
 

Whenever the pivot table changes you can run this procedure to update the chart. To make it run automatically when the pivot table updates, you could put this event procedure into the code module behind the sheet (to open the sheet’s code module, right click the sheet tab, click View Code).

Private Sub Worksheet_Calculate()
  Application.EnableEvents = False

  UpdateChartFromPivotTable

  Application.EnableEvents = True
End Sub
 

This procedure runs every time the worksheet calculates, whether or not the pivot table is involved. To redraw the chart only when mandated by pivot table updates, in Excel 2003 and later you can use this event procedure instead:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Application.EnableEvents = False

  If Target.Name = Me.PivotTables(1).Name Then
    UpdateChartFromPivotTable
  End If

  Application.EnableEvents = True
End Sub
 

You could further modify the code to update different charts which are based on different pivot tables. Modify the indexes in these lines, instead of using “1″.

  Set cht = ActiveSheet.ChartObjects(1).Chart
  Set pt = ActiveSheet.PivotTables(1)
 

I have run through some permutations of the pivot table and chart:

Related Posts:

• Dynamic Chart using Pivot Table and VBA
• Referencing Pivot Table Ranges in VBA
• Pivot Table Conditional Formatting with VBA
• Build an Excel Add-In 2 – Enhanced Functionality
• Add Series to Existing Chart
• Dynamic Chart using Pivot Table and Range Names