Not able to plot Line Chart Row wise For VBA/Excel

Question

Before Starting my problem : I have already tried the available accepted solutions in this community (Creating Line Chart for each Row using VBA excel (Dynamic Row,Column) and Create a new chart for each row using VBA Macros in Excel). For both of these my charts are creating with each line assuming the data column wise.

So I have a data table which usually has values from A1 to F12. The Column A , contains the property name like net profit, sales, cost etc whose chronological progression chart is to be plotted. The Row B2 to F2 contains the year values. The data area is from B3 to F12 which contains the values. In short each row is the changing trend of that particular property. I would like to create a line chart showing this trend (each line in the line chart represents the values in a particular row).

I was able to record a macro which does this very thing

    Worksheets("CashFlow").Activate
    Range("B5:F7").Select
    ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range("'CashFlow'!$B$5:$F$7")
    ActiveSheet.Shapes("Chart 1").IncrementLeft -26.25
    ActiveSheet.Shapes("Chart 1").IncrementTop 133.5
    ActiveWindow.ScrollRow = 2
    ActiveWindow.SmallScroll Down:=5
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).ReversePlotOrder = True
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Cash (In Cr)"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Cash (In Cr)"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 12).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 4).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(5, 8).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    ActiveChart.ChartArea.Select
    ActiveChart.SetElement (msoElementChartTitleNone)
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveChart.FullSeriesCollection(1).XValues = "='CashFlow'!$B$2:$F$2"

However when I am actually running this via macro call (VBA) , it is plotting column wise.

Similarly, the above linked two answers are also charting it column wise when I use their code snippet. How do I write a VBA sub procedure that plots line chart by assuming the data is row wise.

Answer 1

Try this:

ActiveChart.SetSourceData Source:=Range("'CashFlow'!$B$5:$F$7"), PlotBy:=xlRows

Answer 2

I haven't been able to pinpoint your intentions, but here's an example.

Sub test()
    Dim Ws As Worksheet
    Dim rngDB As Range
    Dim rngX As Range, rngY As Range
    Dim i As Integer
    Dim l As Single, t As Single
    Dim w As Single, h As Single
    Dim objChart As ChartObject
    
    Set Ws = Sheets("CashFlow")
    
    For Each objChart In Ws.ChartObjects
        objChart.Delete
    Next objChart
    
    Set rngDB = Ws.Range("a2")
    
    l = 1 'left
    t = Ws.Range("a14").Top 'top
    w = 400 'width
    h = 200      'height
    
    For i = 1 To 10
        Set rngX = rngDB.Offset(0, 1).Resize(1, 5)
        Set rngY = rngDB.Offset(i, 1).Resize(1, 5)
        addmyCharts Ws, rngX, rngY, l, t, w, h
        t = t + h
    Next i
    
End Sub


Private Sub addmyCharts(Ws As Worksheet, rngX As Range, rngY As Range, l As Single, t As Single, w As Single, h As Single)
    Dim shp As Shape
    Dim Cht As Chart
    Dim Srs As Series
    
    Set shp = Ws.Shapes.AddChart(xlLineMarkers, l, t, w, h)
    Set Cht = shp.Chart

    With Cht
        .HasLegend = True
        For Each Srs In .SeriesCollection
            Srs.Delete
        Next Srs
        
        Set Srs = .SeriesCollection.NewSeries
        With Srs
            .XValues = rngX
            .Values = rngY
        End With
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cash (In Cr)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Cash (In Cr)"
    End With
    
End Sub

Sub testChart()
    Dim Ws As Worksheet
    Dim rngDB As Range, Rng As Range
    Dim rngX As Range, rngY As Range
    Dim shp As Shape
    Dim Cht As Chart
    Dim Srs As Series
    Dim l As Single, t As Single
    Dim w As Single, h As Single
    Dim objChart As ChartObject
  
    Set Ws = Sheets("CashFlow")
    
    For Each objChart In Ws.ChartObjects
        objChart.Delete
    Next objChart
    
    Set rngDB = Ws.Range("a3: a12")
    
    l = 1 'left
    t = Ws.Range("a14").Top 'top
    w = 400 'width
    h = 200      'height

    Set shp = Ws.Shapes.AddChart(xlLineMarkers, l, t, w, h)
    Set Cht = shp.Chart

    With Cht
        .HasLegend = True
        For Each Srs In .SeriesCollection
            Srs.Delete
        Next Srs
        
        For Each Rng In rngDB
            Set Srs = .SeriesCollection.NewSeries
            Set rngX = Ws.Range("b2").Resize(1, 5)
            Set rngY = Rng.Offset(0, 1).Resize(1, 5)
            With Srs
                .Name = Rng
                .XValues = rngX
                .Values = rngY
            End With
        Next Rng
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cash (In Cr)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Cash (In Cr)"
    End With
End Sub