Okay I am abit of a copy a paste and hope tings work kind of person when it comes to VBA. I am learning but slowly. I have found a macro to automate the generation of multiple series of XY data in an XY scatter chart. Unfortunately it only applies to three single columns of data representing the A series name, B= the Y data and C= the X data. everything about this is good except I need the X and Y data to represent ranges ie X = columns H:L and Y = columns B:F. I have pasted the code i have for the three single columns below i am hoping it will be just a simple adjustment to that code, I'm sure i could figure it out myself with a little more VBA expertise.
Cheers
Sub OnePointPerXYSeries()
Dim rData As Range
Dim rRow As Range
Dim chtChart As Chart
Application.ScreenUpdating = False
' define chart data region
Set rData = ActiveCell.CurrentRegion
' create an embedded chart, about how Excel does
Set chtChart = ActiveSheet.ChartObjects.Add(Application.UsableWidth / 4, _
Application.UsableHeight / 4, Application.UsableWidth / 2, Application.UsableHeight / 2).Chart
chtChart.ChartType = xlXYScatter
' remove chart series (we'll add our own)
Do While chtChart.SeriesCollection.Count > 0
chtChart.SeriesCollection(1).Delete
Loop
' Populate the chart
For Each rRow In rData.Rows
' don't use invalid data
If IsNumeric(rRow.Cells(1, 2)) And IsNumeric(rRow.Cells(1, 3)) Then
' add series & define parameters
With chtChart.SeriesCollection.NewSeries
.Values = rRow.Cells(1, 2)
.XValues = rRow.Cells(1, 3)
.Name = rRow.Cells(1, 1)
End With
End If
Next
Application.ScreenUpdating = True
End Sub
Cheers
Sub OnePointPerXYSeries()
Dim rData As Range
Dim rRow As Range
Dim chtChart As Chart
Application.ScreenUpdating = False
' define chart data region
Set rData = ActiveCell.CurrentRegion
' create an embedded chart, about how Excel does
Set chtChart = ActiveSheet.ChartObjects.Add(Application.UsableWidth / 4, _
Application.UsableHeight / 4, Application.UsableWidth / 2, Application.UsableHeight / 2).Chart
chtChart.ChartType = xlXYScatter
' remove chart series (we'll add our own)
Do While chtChart.SeriesCollection.Count > 0
chtChart.SeriesCollection(1).Delete
Loop
' Populate the chart
For Each rRow In rData.Rows
' don't use invalid data
If IsNumeric(rRow.Cells(1, 2)) And IsNumeric(rRow.Cells(1, 3)) Then
' add series & define parameters
With chtChart.SeriesCollection.NewSeries
.Values = rRow.Cells(1, 2)
.XValues = rRow.Cells(1, 3)
.Name = rRow.Cells(1, 1)
End With
End If
Next
Application.ScreenUpdating = True
End Sub