Why is my stacked bar and line chart having different series assignments in VBA than from when I did it manually?

Question

I am trying to create a stacked bar chart with a line series on top of it. However when I move from creating the stacked chart manually to vba it changes the series to make the chart unusable for my purpose. It changes the categories I want on the x-axis to stacked sections, and the stacked categories to the x-axis sections.

Typically I work with data transfer and automated form filling, so I'm no expert at graph making in vba. I recorded the code below and adjusted it so it would fit my variable data range (n is the row category). It selects the correct data, just switches the axis.

    ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
    ActiveChart.SetSourceData Source:=Range(Worksheets(name).Range("B1"), Worksheets(name).Range("B2").Offset(n - 1, 2))

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(n + 1).name = "Takt"
    ActiveChart.FullSeriesCollection(n + 1).Values = Range(Worksheets(name).Range("E1"), Worksheets(name).Range("E2").Offset(n - 1, 0))
    ActiveChart.FullSeriesCollection(n + 1).ChartType = xlLine
    ActiveChart.ChartTitle.Text = "Yamazumi"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Yamazumi"

Answer 1

'i use Me instead WorkSheets(name) to reproduce...

Sub TestGraph()
   Dim n As Long
   'for the data in picture the n have to be 2
   n = 2
   'From the picture of "right" graph: you don't need headers, so B1 => B2
   'also need range B2:D4 so Offset(n-1, 2) => Offset(n, 2)
   'AND FIRST select the data
   Me.Range(Me.Range("B2"), Me.Range("B2").Offset(n, 2)).Select
   
   Me.Shapes.AddChart2(297, xlColumnStacked).Select
   'ActiveChart.SetSourceData Source:=Range(Worksheets(name).Range("B1"), Worksheets(name).Range("B2").Offset(n - 1, 2))


    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(n + 1).Name = "Takt"
    ActiveChart.FullSeriesCollection(n + 1).values = Me.Range(Me.Range("E2"), Me.Range("E2").Offset(n, 0))
    ActiveChart.FullSeriesCollection(n + 1).ChartType = xlLine
    ActiveChart.ChartTitle.text = "Yamazumi"
    Selection.Format.TextFrame2.TextRange.Characters.text = "Yamazumi"
End Sub

now try not to work with "Select" or "Selected" anything... It is good practice to explicitly mention the object e.g.

Sub TestGraph()
   Dim n As Long, shp As Object
   n = 2
   Me.Range(Me.Range("B2"), Me.Range("B2").Offset(n, 2)).Select

   Set shp = Me.Shapes.AddChart2(297, xlColumnStacked)

   With shp.Chart
    .SeriesCollection.NewSeries
    .FullSeriesCollection(n + 1).Name = "Takt"
    .FullSeriesCollection(n + 1).values = Me.Range(Me.Range("E2"), Me.Range("E2").Offset(n, 0))
    .FullSeriesCollection(n + 1).ChartType = xlLine
    .ChartTitle.text = "Yamazumi"
    End With
End Sub

Use the Headers and remove the early "Select" of data:

Sub TestGraph()
   Dim n As Long, shp As Object
   n = 2
   'Me.Range(Me.Range("B1"), Me.Range("B2").Offset(n, 2)).Select
   Set shp = Me.Shapes.AddChart2(297, xlColumnStacked)
   With shp.Chart
    .SetSourceData Source:=Me.Range(Me.Range("B1"), Me.Range("B2").Offset(n, 2))
    .SeriesCollection.NewSeries
    .FullSeriesCollection(n + 1).Name = "Takt"
    .FullSeriesCollection(n + 1).values = Me.Range(Me.Range("E2"), Me.Range("E4"))
    .FullSeriesCollection(n + 1).ChartType = xlLine
    .ChartTitle.text = "Yamazumi"
    End With
End Sub