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...]
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.