Hi there,

I'm trying to create charts, where the y-data for each chart is a
different row, but the x-data is fixed. I'd like to create an x-y
scatter chart and create each chart as a new sheet, labeled with the
item in Col A. (Y data is in cols B-D, X data is in Row 1). Lastly,
I'd like to add a linear trendline to each.

I've been working unsuccessfully with macros. Any help would be much
appreciated.

Hi,

Here is some code to get you started.

Sub Macro3()
'
Dim lngRow As Long
Dim shtData As Worksheet
Dim strData As String

Set shtData = Worksheets("Sheet1")
lngRow = 2

Do While shtData.Cells(lngRow, 1) < ""
With Charts.Add
.Location Whe=xlLocationAsNewSheet
.ChartType = xlXYScatter
strData = "B1:D1,B" & lngRow & ":D" & lngRow
.SetSourceData Source:=shtData.Range(strData), _
PlotBy:=xlRows
.SeriesCollection(1).Name = _
"='" & shtData.Name & "'!R" & lngRow & "C1"
.HasTitle = True
.ChartTitle.Characters.Text = .SeriesCollection(1).Name
.HasLegend = False
.SeriesCollection(1).Trendlines.Add _
Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, _
DisplayRSquared:=False
End With
lngRow = lngRow + 1
Loop
End Sub

Cheers
Andy

wrote:
Hi there,

I'm trying to create charts, where the y-data for each chart is a
different row, but the x-data is fixed. I'd like to create an x-y
scatter chart and create each chart as a new sheet, labeled with the
item in Col A. (Y data is in cols B-D, X data is in Row 1). Lastly,
I'd like to add a linear trendline to each.

I've been working unsuccessfully with macros. Any help would be much
appreciated.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Thanks for that help.

Can anyone also help me to combine everything on 1 graph (but now each
row = 1 series) and help me take the equations generated by the linear
fit and put them into the worksheet, alongside the adjacent row number?

Thanks.

Andy Pope wrote:
Hi,

Here is some code to get you started.

Sub Macro3()
'
Dim lngRow As Long
Dim shtData As Worksheet
Dim strData As String

Set shtData = Worksheets("Sheet1")
lngRow = 2

Do While shtData.Cells(lngRow, 1) < ""
With Charts.Add
.Location Whe=xlLocationAsNewSheet
.ChartType = xlXYScatter
strData = "B1:D1,B" & lngRow & ":D" & lngRow
.SetSourceData Source:=shtData.Range(strData), _
PlotBy:=xlRows
.SeriesCollection(1).Name = _
"='" & shtData.Name & "'!R" & lngRow & "C1"
.HasTitle = True
.ChartTitle.Characters.Text = .SeriesCollection(1).Name
.HasLegend = False
.SeriesCollection(1).Trendlines.Add _
Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, _
DisplayRSquared:=False
End With
lngRow = lngRow + 1
Loop
End Sub

Cheers
Andy

wrote:
Hi there,

I'm trying to create charts, where the y-data for each chart is a
different row, but the x-data is fixed. I'd like to create an x-y
scatter chart and create each chart as a new sheet, labeled with the
item in Col A. (Y data is in cols B-D, X data is in Row 1). Lastly,
I'd like to add a linear trendline to each.

I've been working unsuccessfully with macros. Any help would be much
appreciated.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Ok, this does rows as series in 1 chart

Sub Macro1()
'
'
Dim lngRow As Long
Dim shtData As Worksheet

Set shtData = Worksheets("Sheet2")
lngRow = 2

With Charts.Add
.ChartType = xlXYScatterLines
Do While .SeriesCollection.Count 0
.SeriesCollection(1).Delete
Loop
Do While shtData.Cells(lngRow, 1) < ""
With .SeriesCollection.NewSeries
.XValues = "='" & shtData.Name & "'!R1C2:R1C4"
.Values = "='" & shtData.Name & _
"'!R" & lngRow & "C2:R" & lngRow & "C4"
.Name = "='" & shtData.Name & "'!R" & lngRow & "C1"
End With
lngRow = lngRow + 1
Loop
End With

End Sub

Cheers
Andy

wrote:
Thanks for that help.

Can anyone also help me to combine everything on 1 graph (but now each
row = 1 series) and help me take the equations generated by the linear
fit and put them into the worksheet, alongside the adjacent row number?

Thanks.

Andy Pope wrote:

Hi,

Here is some code to get you started.

Sub Macro3()
'
Dim lngRow As Long
Dim shtData As Worksheet
Dim strData As String

Set shtData = Worksheets("Sheet1")
lngRow = 2

Do While shtData.Cells(lngRow, 1) < ""
With Charts.Add
.Location Whe=xlLocationAsNewSheet
.ChartType = xlXYScatter
strData = "B1:D1,B" & lngRow & ":D" & lngRow
.SetSourceData Source:=shtData.Range(strData), _
PlotBy:=xlRows
.SeriesCollection(1).Name = _
"='" & shtData.Name & "'!R" & lngRow & "C1"
.HasTitle = True
.ChartTitle.Characters.Text = .SeriesCollection(1).Name
.HasLegend = False
.SeriesCollection(1).Trendlines.Add _
Type:=xlLinear, Forward:=0, _
Backward:=0, DisplayEquation:=False, _
DisplayRSquared:=False
End With
lngRow = lngRow + 1
Loop
End Sub

Cheers
Andy

wrote:

Hi there,

I'm trying to create charts, where the y-data for each chart is a
different row, but the x-data is fixed. I'd like to create an x-y
scatter chart and create each chart as a new sheet, labeled with the
item in Col A. (Y data is in cols B-D, X data is in Row 1). Lastly,
I'd like to add a linear trendline to each.

I've been working unsuccessfully with macros. Any help would be much
appreciated.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info