Automating production of graphs using VBA

Question

I need to make a lot of graphs and am attempting to avoid manually developing and formatting them. Data For data protection, I have removed the row labels from an image of the data in the previous section. For each time series row of data, I need a line graph, with the title of each graph being the label for the row in the leftmost column and the x axis labels being the labels for the columns. I can make graphs using a for loop, but I'm having trouble assigning labels to the x axis and chart titles. I've read other threads, but the fixes don't seem to be working. Here is the code as of now:

Sub main()

    Dim i As Long
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim chrt As Chart
    Dim chrtname As String

    'Find the last used row
    'LastRow = Sheets("Chart Data").Range("A1").End(xlUp).Row
    LastRow = 272
    'Find the last used column
    'LastColumn = Sheets("Chart Data").Range("A1").End(xlToRight).Column
    LastColumn = 15
    'Looping from second row till last row which has the data
    For i = 86 To LastRow
        Sheets("Sheet2").Select

        'Adds chart to the sheet
        Set chrt = Sheets("Sheet2").Shapes.AddChart.Chart
        'sets the chart type
        chrt.ChartType = xlLine

        'adding line chart
        With Sheets("Charts Data")
            chrt.SetSourceData Source:=.Range(.Cells(i, 3), .Cells(i, LastColumn))
        End With

        'adjust the position of chart on sheet
        chrt.ChartArea.Left = 1
        chrt.ChartArea.Top = (i - (i *0.5)) * chrt.ChartArea.Height
        
        'Trying to set a chart title
        chrt.HasTitle = True
        chrtname = Cells(i, 2).Value
        chrt.SetElement (msoElementChartTitleAboveChart)
        chrt.ChartTitle.Select
        chrt.ChartTitle.Text = chrtname
        
        'Trying to add x axis labels
        chrt.SeriesCollection(1).XValues = "=Charts Data!$C$3:$O$3"
    
    
        Next

End Sub

The chart title appears to get deleted when trying to assign a cell value in all other cases, which is a similar issue to what one other user seemed to be experiencing. How to use VBA to make a cell value the title of a chart

Any advice would be appreciated because this was resolved confidentially. I also need to include an additional data series that is consistent across all charts, but I haven't been able to do that either.

I'm sorry if this is a very simple question, but I'm new to VBA.

Answer 1

You have to change:

chrtname = Cells(i, 2).Value

To:

chrtname = Sheets("Charts Data").Cells(i, 2).Value

This will work.