Dynamic Chart Series for Pivot Table

**cricrazy** · 03-26-2009, 05:06 PM

I use pivot table and X-Y Scatter plot for analysis of data. Is it possible for scatter plot to select the chart series automatically, as I select the different pivot table fields?

I use x values as the 'Row Field', and Y values with 'Data Items'. And 'Column Field' as a different series. (Please check first figure [pivot.jpg])

Is it possible to write a macro which automatically plots
column field - as Series Name
Row field - as X values
data items - as Y values

(I have figured out how to plot X-Y Scatter plot for Pivot table, which is generally not allowed.)

Consequently, Is it possible to do that same if there are more than one column fields? (Check Figure 2 [pivot2.jpg])

I have worked with excel macros in the past. But for this particular problem, I am not able to come up with any logic so far. So, please help.

I hope that I have explained the problem clearly. Thanks.

**Andy Pope** · 03-27-2009, 06:58 AM

It may help if you posted example workbook rather than pictures

**cricrazy** · 03-27-2009, 05:51 PM

Here is the attached sample file. Sorry for putting only pictures. Thanks.

**Andy Pope** · 03-28-2009, 07:51 AM

Right click the sheet tab and add this code

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim chtTemp As Chart
    Dim rngXData As Range
    Dim rngYData As Range
    Dim lngSeries As Long
    
    If Target.ColumnFields.Count = 0 Then Exit Sub
    If Application.WorksheetFunction.CountA(Target.DataBodyRange) = 0 Then Exit Sub
    Set chtTemp = ActiveSheet.ChartObjects(1).Chart
    Set rngXData = Target.RowRange.Offset(1).Resize(Target.RowRange.Rows.Count - 1)
    
    For Each rngYData In Target.DataBodyRange.Columns
        lngSeries = lngSeries + 1
        If chtTemp.SeriesCollection.Count < lngSeries Then
            chtTemp.SeriesCollection.NewSeries
        End If
        With chtTemp.SeriesCollection(lngSeries)
            .ChartType = xlColumnClustered
            .Name = Target.ColumnRange.Columns(lngSeries)
            .Values = rngYData
            .XValues = rngXData
            .ChartType = xlXYScatter
        End With
    Next
    Do While chtTemp.SeriesCollection.Count > lngSeries
        With chtTemp.SeriesCollection(chtTemp.SeriesCollection.Count)
            .ChartType = xlColumnClustered
            .Delete
        End With
    Loop
    
End Sub

**cricrazy** · 03-28-2009, 02:11 PM

Thank you so much Andy.

Just one more question.

When we have more than one 1 columnfields (for example, Pressure and Diameter), Is it possible for series names to be like

pressure 1 diameter 1
Pressure 1 diameter 2
pressure 2 diamater 1
Pressure 2 diameter 2

(I usually remove columns with subtotals from field setting --> subtotals --> none)

I am attaching the excel sheet with updated graph. Once again, thanks a lot.

**Andy Pope** · 03-29-2009, 07:36 AM

Try this,

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim chtTemp As Chart
    Dim rngXData As Range
    Dim rngYData As Range
    Dim lngSeries As Long
    Dim lngHeaderRow As Long
    Dim strName As String
    Dim lngCol As Long
    
    If Target.ColumnFields.Count = 0 Then Exit Sub
    If Application.WorksheetFunction.CountA(Target.DataBodyRange) = 0 Then Exit Sub
    Set chtTemp = ActiveSheet.ChartObjects(1).Chart
    Set rngXData = Target.RowRange.Offset(1).Resize(Target.RowRange.Rows.Count - 1)
    
    ReDim strColumnFields(1 To Target.ColumnFields.Count) As String
    For lngCol = 1 To Target.ColumnFields.Count
        strColumnFields(lngCol) = Target.ColumnRange.Cells(1, lngCol)
    Next
    
    ReDim strColumnHeaders(1 To Target.ColumnFields.Count) As String
    
    For Each rngYData In Target.DataBodyRange.Columns
    
        lngSeries = lngSeries + 1
        strName = ""
        For lngHeaderRow = 1 To Target.ColumnFields.Count
            If Len(Target.ColumnRange.Cells(lngHeaderRow + 1, lngSeries)) > 0 Then
                strColumnHeaders(lngHeaderRow) = Target.ColumnRange.Cells(lngHeaderRow + 1, lngSeries)
            End If
            strName = strName & strColumnFields(lngHeaderRow) & " " & strColumnHeaders(lngHeaderRow) & " "
        Next
        
        If chtTemp.SeriesCollection.Count < lngSeries Then
            chtTemp.SeriesCollection.NewSeries
        End If
        With chtTemp.SeriesCollection(lngSeries)
            .ChartType = xlColumnClustered
            .Name = Trim(strName)
            .Values = rngYData
            .XValues = rngXData
            .ChartType = xlXYScatter
        End With
    Next
    Do While chtTemp.SeriesCollection.Count > lngSeries
        With chtTemp.SeriesCollection(chtTemp.SeriesCollection.Count)
            .ChartType = xlColumnClustered
            .Delete
        End With
    Loop
    
End Sub