Automating production of graphs using VBA

Question

I have a large number of graphs to produce and am trying to avoid creating and formatting them manually. Data I have linked an image of the data above, with the row labels omitted for data security. I need a line graph for each time series row of data, where the title of each graph is the row label in leftmost column and x axis labels are the column labels. I am able to create graphs in a for loop but am running into issues when assigning chart titles and x axis labels. I've read other posts and the fixes haven't helped. The code so far is as follows:

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

If I type text for the chart title in "" it works, but otherwise the chart title seems to get deleted when trying to assign a cell value, a similar problem another user seemed to have. How to make a cell value as chart title using vba

This was resolved privately so any guidance appreciated. I also need to add in an extra data series which is the same across all charts but am yet to succeed at that either.

I apologise as this is likely a very basic question but I am new to VBA.

I would look to using a template sheet with the chart set to pick up the labels from specific cells. — Solar Mike
– Solar Mike, Commented Jan 22, 2023 at 15:00

user3598756 · Accepted Answer · 2023-01-22 14:58:50Z

for the title issue, you have to change:

chrtname = Cells(i, 2).Value

to:

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

since without explicit reference to sheet parent object, a range object would implicitly assume the currently active sheet, which you made to be "Sheet2" by mans of the Sheets("Sheet2").Select statement

while as to the XValues issue, you have to change:

chrt.SeriesCollection(1).XValues = "=Charts Data!$C$3:$O$3"

to:

chrt.SeriesCollection(1).XValues = "='Charts Data'!$C$3:$Q$3"

since a sheet name with spaces must be enclosed by single quotation marks

But you might want to consider this sort of a refactoring of your code which extensively uses the With .. End With construct to have better grasp on which object is currently under you "hold"

Option Explicit

Sub main()

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

    '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
    
    With Sheets("Charts Data") ' reference data sheet
        For i = 86 To LastRow
    
            'Adds chart to the Sheet2
            Set chrt = Sheets("Sheet2").Shapes.AddChart.Chart
            
            'adding line chart
            chrt.SetSourceData Source:=.Range(.Cells(i, 3), .Cells(i, LastColumn))
            
            With chrt ' reference the just added chart

                'sets the chart type
                .ChartType = xlLine
        
                'adjust the position of chart on sheet
                .ChartArea.Left = 1
                .ChartArea.Top = (i - (i * 0.5)) * chrt.ChartArea.Height
                
                'Trying to set a chart title
                .HasTitle = True
                
                'add x axis labels
                .SeriesCollection(1).XValues = "='Charts Data'!$C$3:$Q$3"

            End With
            
            chrt.ChartTitle.Text = .Cells(i, 2).Value
            
        Next
    End With

End Sub

Collectives™ on Stack Overflow

Automating production of graphs using VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related