PDA

View Full Version : XY Scatter Plot from Pivot



Onetrack
02-15-2012, 10:32 AM
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
02-16-2012, 02:33 AM
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
02-16-2012, 04:28 AM
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
02-16-2012, 09:23 AM
Andy - that's amazing...! Brilliant coding... - thank you!:hi: :cloud9:

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