Creating Pivot Charts in Excel 2007:-

A pivot chart is a chart that uses a pivot table as the underlying data source. As we know, pivot charts don’t have the cool “show pages” functionality that regular pivot tables have. You can overcome this problem with a quick VBA macro that creates a pivot table and then a pivot chart based on the pivot table. The macro then adds the customer field to the page field of the pivot table. It then loops through each customer and exports the chart for each customer.

In Excel 2007, you first create a pivot cache by using the PivotCache.Create method. You can then define a pivot table based on the pivot cache. The usual procedure is to turn off pivot table updating while you add fields to the pivot table. Then you update the pivot table in order to have Excel perform the calculations.

It takes a bit of finesse to figure out the final range of the pivot table. If you have turned off the column and row totals, the chartable area of the pivot table starts one row below the PivotTableRange1 area. You have to resize the area to include one fewer row in order to make your chart appear correctly.

After the pivot table is created, you can switch back to the Charts.Add code. You can use any formatting code to get the chart formatted as you desire.

The following code creates a pivot table and a single pivot chart that summarize revenue by region and product:

Sub CreateSummaryReportUsingPivot()

Dim WSD As Worksheet

Dim PTCache As PivotCache

Dim PT As PivotTable

Dim PRange As Range

Dim FinalRow As Long

Dim ChartDataRange As Range

Dim Cht As Chart

Set WSD = Worksheets(“Data”)

‘Delete any prior pivot tables

For Each PT In WSD.PivotTables

PT.TableRange2.Clear

Next PT

WSD.Range(“I1:Z1”).EntireColumn.Clear

‘ Define input area and set up a Pivot Cache

FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

FinalCol = WSD.Cells(1, Application.Columns.Count). _End(xlToLeft).Column

Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)

Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:= _xlDatabase, SourceData:=PRange.Address)

‘ Create the Pivot Table from the Pivot Cache

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _Cells(2, FinalCol + 2), TableName:=”PivotTable1”)

‘ Turn off updating while building the table

PT.ManualUpdate = True

‘ Set up the row fields

PT.AddFields RowFields:=”Region”, ColumnFields:=”Product”, _PageFields:=”Customer”

‘ Set up the data fields

With PT.PivotFields(“Revenue”)

.Orientation = xlDataField

.Function = xlSum

.Position = 1

End With

With PT

.ColumnGrand = False

.RowGrand = False

.NullString = “0”

End With

‘ Calc the pivot table

PT.ManualUpdate = False

PT.ManualUpdate = True

‘ Define the Chart Data Range

Set ChartDataRange = _PT.TableRange1.Offset(1, 0).Resize(PT.TableRange1.Rows.Count - 1)

‘ Add the Chart

WSD.Shapes.AddChart.Select

Set Cht = ActiveChart

Cht.SetSourceData Source:=ChartDataRange

‘ Format the Chart

Cht.ChartType = xlColumnClustered

Cht.SetElement (msoElementChartTitleAboveChart)

Cht.ChartTitle.Caption = “All Customers”

Cht.SetElement msoElementPrimaryValueAxisThousands

End Sub

Figure (given below) shows the resulting chart and pivot table.



(VBA creates a pivot table and then a chart from the pivot table. Excel automatically displays the PivotChart Filter pane in response.)