issues with setsourcedata

[krishnan v]

I am new and am going step by step. I have 5 columns with the first column always being the x axis and the others will be the y axis. I will end up with 4 XY scatter charts ultimately.

DOS	flow	temperature	pressure	level
	m3/hr	deg C	bar	%
1.00​	100​	60​	14.7​	51​
2.00​	200​	50​	14.7​	51​
3.00​	1400​	60​	14.7​	50​
4.00​	1200​	60​	14.7​	53​
5.00​	5500​	50​	14.7​	55​
6.00​	230​	60​	1.8​	51​
7.00​	230​	50​	14.7​	52​
8.00​	230​	60​	14.8​	54​
9.00​	222​	60​	14.7​	56​
10.00​	222​	50​	14.7​	55​
11.00​	222​	60​	14.7​	53​
12.00​	222​	50​	14.7​	52​
13.00​	555​	60​	14.7​	51​
14.00​	222​	60​	14.7​	50​

Before i moved over to looping, i tried using range values and came up with the following code, however while plotting the chart, it tends to add all the series to the same chart and not just one. How do I get over this.

Second is how do I proceed to looping, ie. get 4 charts with the same X axis and different y axis

Sub krishcharttrial()
' defining the integers for iterations
Dim a As Integer
Dim b As Integer


Dim i As String
Dim j As String

Dim x As String
Dim y As String
Dim z As String

x = Cells(1, 4)
y = Cells(2, 4)
z = Cells(1, 1)

i = Range(Cells(3, 1), Cells(16, 1)).Address
j = Range(Cells(3, 4), Cells(16, 4)).Address


ActiveSheet.Shapes.AddChart.Select
ActiveChart.charttype = xlXYScatter
ActiveChart.SetSourceData Source:=Range(i, j)


' chart title
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = x & " vs " & y
ActiveChart.Legend.Position = xlBottom
ActiveChart.Axes(xlValue).TickLabels.Font.Bold = True
ActiveChart.Axes(xlCategory).TickLabels.Font.Bold = True
ActiveChart.Axes (xlValue)

 
'Add X-axis title
  ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
  ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = z
'Add y-axis title
  ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = y



'Add Major Gridlines
  ActiveChart.SetElement (msoElementPrimaryValueGridLinesMajor)
    ActiveChart.Location where:=xlLocationAsNewSheet, Name:=x
    ActiveSheet.Move After:=Sheets(Sheets.Count)
    Sheets("sheet1").Select
   

End Sub

 

[Domenic]

The following code assumes that the worksheet containing the source data is the active sheet...

Option Explicit

Sub Create_XY_Charts()

    Dim sourceDataRange As Range
    Set sourceDataRange = ActiveSheet.UsedRange
    
    Dim destinationWorksheet As Worksheet
    Set destinationWorksheet = Worksheets.Add(after:=Sheets(Sheets.Count))
    
    Dim leftPos As Single
    leftPos = destinationWorksheet.Range("B2").Left
    
    Dim topPos As Single
    topPos = destinationWorksheet.Range("B2").top
    
    Dim X_Axis_Title As String
    X_Axis_Title = sourceDataRange(1, 1).Value
    
    Dim XRange As Range
    With sourceDataRange
        Set XRange = .Offset(2, 0).Resize(.Rows.Count - 2, 1)
    End With
    
    Dim Y_Axis_Title As String
    Dim YRange As Range
    Dim columnIndex As Long
    With sourceDataRange
        For columnIndex = 2 To .Columns.Count
            Y_Axis_Title = .Cells(1, columnIndex).Value & " " & .Cells(2, columnIndex).Value
            Set YRange = .Offset(2, columnIndex - 1).Resize(.Rows.Count - 2, 1)
            CreateChart destinationWorksheet, X_Axis_Title, XRange, Y_Axis_Title, YRange, leftPos, topPos
        Next columnIndex
    End With
    
    Set sourceDataRange = Nothing
    Set destinationWorksheet = Nothing
    Set XRange = Nothing
    Set YRange = Nothing
    
End Sub

Private Sub CreateChart(ByVal destinationWorksheet As Worksheet, ByVal X_Axis_Title As String, ByVal XRange As Range, _
                        ByVal Y_Axis_Title As String, ByVal YRange As Range, ByRef leftPos As Single, ByRef topPos As Single)
                        
    Dim Gap As Integer
    Gap = 20 'between charts
                        
    Dim chartObj As ChartObject
    Set chartObj = destinationWorksheet.ChartObjects.Add(Left:=leftPos, top:=topPos, Width:=-1, Height:=-1)
    
    With chartObj.Chart
        .HasTitle = True
        .chartTitle.Text = X_Axis_Title & " vs " & Y_Axis_Title
        .ChartType = xlXYScatter
        With .SeriesCollection.NewSeries
            .Name = "=""" & Y_Axis_Title & """"
            .XValues = XRange
            .Values = YRange
        End With
        With .Axes(Type:=xlCategory, AxisGroup:=xlPrimary)
            .HasTitle = True
            .AxisTitle.Text = X_Axis_Title
            .TickLabels.Font.Bold = True
        End With
        With .Axes(Type:=xlValue, AxisGroup:=xlPrimary)
            .HasTitle = True
            .AxisTitle.Text = Y_Axis_Title
            .TickLabels.Font.Bold = True
        End With
        .SetElement msoElementPrimaryValueGridLinesMajor
        .Legend.Position = xlLegendPositionBottom
    End With
    
    topPos = topPos + chartObj.Height + Gap
    
    Set chartObj = Nothing
    
End Sub

If you run into any issues, or if you have any questions, please let me know.

Hope this helps!