XY Scatter Plot from Pivot

[Onetrack]

Hello all,

I have attached a spreadsheet showing a pivot that uses only one columnfield count.

Does anyone know how to modify Andy Pope's code such that the XY scatterplot can be run in this type of report?

thankyou, John

[Andy Pope]

I moved the charting code to a standard module and call that from the pivot table update event.
I also create both variations for plotting the xy columns as a single series or as multiple single point row series.

Module code

Public Sub Plot_XYScatterFromPT_ByColumn(Target As PivotTable, Cht As Chart)

    Dim chtTemp As Chart
    Dim rngXData As Range
    Dim rngYData As Range
    Dim lngHeaderRow As Long
    Dim strName As String
    
    If Target.ColumnFields.Count = 0 Then Exit Sub
    If Application.WorksheetFunction.CountA(Target.DataBodyRange) = 0 Then Exit Sub
    
    Set chtTemp = Cht
    Set rngXData = Target.DataBodyRange.Columns(1)
    Set rngYData = Target.DataBodyRange.Columns(2)
    strName = Target.ColumnFields(1).Name
    
    With chtTemp
        Do While .SeriesCollection.Count > 1
            .SeriesCollection(2).Delete
        Loop
        With chtTemp.SeriesCollection(1)
            .Name = Trim(strName)
            .Values = rngYData
            .XValues = rngXData
            .ChartType = xlXYScatter
        End With
    End With

End Sub
Public Sub Plot_XYScatterFromPT_ByRow(Target As PivotTable, Cht As Chart)

    Dim chtTemp As Chart
    Dim rngXData As Range
    Dim rngYData As Range
    Dim lngSeries 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 = Cht
    Set rngXData = Target.DataBodyRange.Columns(1)
    Set rngYData = Target.DataBodyRange.Columns(2)
    
    With chtTemp
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
    
        For lngSeries = 1 To Target.DataBodyRange.Rows.Count
            strName = ""
            For lngCol = 1 To Target.RowFields.Count
                If Len(Target.RowRange.Cells(lngSeries + 1, lngCol)) > 0 Then
                    strName = Trim(strName & " " & Trim(Target.RowRange.Cells(lngSeries + 1, lngCol)))
                End If
            Next
            With .SeriesCollection.NewSeries
                .Name = strName
                .Values = rngYData.Rows(lngSeries)
                .XValues = rngXData.Rows(lngSeries)
                .ChartType = xlXYScatter
            End With
        Next
    End With

End Sub

Sheet code

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Plot_XYScatterFromPT_ByColumn Target, ActiveSheet.ChartObjects("Chart 1").Chart
    Plot_XYScatterFromPT_ByRow Target, ActiveSheet.ChartObjects("Chart 4").Chart
    
End Sub

[theta]

Not related to your original thread, but if you are doing fancy things with graphs and plot...check out this guys site. I can't even begin to comprehend how he does half the graphing, it is amazing!

www.excelunusual.com

[Onetrack]

Andy - that's amazing...! Brilliant coding... - thank you!

[theta - I'll take a look - thank you also...]