I'm trying to create a VBA macro which generates multiple line graphs. I'm having an issue with the selection of the graph due to the iteration through each row.
I think my issue lies here
I've changed it multiple times but I can't get a successful result. Basically, {B2:T2} is the row header and is the same for each chart. The second half is the part I'm having an issue getting to work. Basically, I want Row 3 to plot, then Row 4, etc. I'm sure i'm probably missing something basic but I can't figure it out. The only success I've had is where it would select header and row 3, then header, Row 3, and row4, etc. I just want two rows selected each time.
Code:
Sub GenerateCharts()
Dim i As Integer
NumRows = Range("B3", Range("B3").End(xlDown)).Rows.Count
NumRows = NumRows + 2
For i = 3 To NumRows
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Sheets("Mike").Range("B2:T2", Range(Cells(i, 2), Cells(i, 2).End(xlToRight)).Select)
ActiveChart.ChartType = xlLineMarkers
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = (A1)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
.Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "mmm-yy"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Efficiency"
.Axes(xlValue, xlPrimary).TickLabels.NumberFormat = "0.0%"
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
.HasDataTable = False
End With
Cells(1, 1).Select
Next i
End Sub
I think my issue lies here
Code:
ActiveChart.SetSourceData Source:=Sheets("Mike").Range("B2:T2", Range(Cells(i, 2), Cells(i, 2).End(xlToRight)).Select)
I've changed it multiple times but I can't get a successful result. Basically, {B2:T2} is the row header and is the same for each chart. The second half is the part I'm having an issue getting to work. Basically, I want Row 3 to plot, then Row 4, etc. I'm sure i'm probably missing something basic but I can't figure it out. The only success I've had is where it would select header and row 3, then header, Row 3, and row4, etc. I just want two rows selected each time.