Automate creation of charts

Hi all,

I have a spreadsheet of car park occupancy for each hour of the day across 5 car parks. The first 24 rows are hourly occupancies for Car Park A, the next for Car Park B, and so on.
I want a chart of hourly occupancy for each of the 5 car parks in the spreadsheet and automate this process.
I know I'd need a for loop which creates the first chart and then adds 24 rows to the chart's data array, but struggling to get my head around this.

The code I have so far is as follows:

 
Sub BuildCharts()

Dim x As Integer

For x = 0 To 5
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select                            'add chart (specific type)
    ActiveChart.SeriesCollection.NewSeries                                      'create data series
    ActiveChart.FullSeriesCollection(1).Values = "='2022 Occupancy'!$B$2:$B$25" 'series data for 1st car park is C2:C25, but each car park will have to add 24 rows to the series data array
    ActiveChart.FullSeriesCollection(1).XValues = "='2022 Occupancy'!$C$2:$C$25" 'X Axis (24 rows of hourly increments) could do with incrementing by 24 rows as well for consistency
    ActiveChart.ChartTitle.Select
    Selection.Caption = "='2022 Occupancy'!R2C1"                                'Set chart title to car park name (first cell in column A, but needs to increment by 24 rows as well as each 24 rows, there is a new car park)
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    Application.CutCopyMode = False
    Selection.Caption = "='2022 Occupancy'!R1C3"                                'Set X axis to 'Time Periods' (C1)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    Application.CutCopyMode = False
    Selection.Caption = "='2022 Occupancy'!R1C2"                                'Set Y axis to 'Occupancy' (B1)
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Line                                                  'Format line as red
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
    x = x + 24
Next x

End Sub

Any help would be hugely appreciated

Well first thing I noticed is you told the For Loop to count x from 0 to 5, but at the end of the loop, you add 24 to x, meaning it already exceeds 5 and ends the loop.

Thanks, I see what you mean. Should be "For x=1 To 5" and "x=x+1" but within the code, determining the chart series data could use a range of x*24-22 to x*24+1
So for iteration 1 (x=1), 1*24-22 to 1*24+1 (i.e. row 2 to row 25)
and for iteration 2 (x=2), 2*24-22 to 2*24+1 (i.e. row 26 to row 49)

Just need the code that achieves that!

You might need to enable the Microsoft Scripting Runtime library to use the following macro. To do this, in the VBA editor goto Tools > References. Then scroll til you see "Microsoft Scripting Runtime library" and make sure it's checked. If already checked it will be at the top of the list with the other active references.

Sub BuildCharts()

Dim x As Integer
Dim dict As New Scripting.Dictionary   'Create dictionary object
Dim i As Integer

'Populate dictionary with row numbers
For i = 2 To 121 Step 24
    dict.Add i, i + 23
Next i

For Each Key In dict.keys
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(1).Values = "='2022 Occupancy'!$B$" & Key & ":$B$" & dict(Key) 'set series data range for the current car park
    ActiveChart.FullSeriesCollection(1).XValues = "='2022 Occupancy'!$C$" & Key & ":$C$" & dict(Key) 'set X axis range for the current car park
    ActiveChart.ChartTitle.Caption = "='2022 Occupancy'!R" & Key & "C1" 'set chart title for the current car park
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlCategory).AxisTitle.Caption = "='2022 Occupancy'!R1C3" 'set X axis title
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.Axes(xlValue).HasTitle = True 'create Y-axis title object if it doesn't exist
    ActiveChart.Axes(xlValue).AxisTitle.Caption = "='2022 Occupancy'!R1C2" 'set Y axis title
    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
    End With
Next Key

End Sub

Hope this helps!

Hero, thanks very much

Less complicated than using a dictionary:

For x = 1 To 5
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select                            
    ActiveChart.SeriesCollection.NewSeries                                      
    ActiveChart.FullSeriesCollection(1).Values = Worksheets("2022 Occupancy").Range("B2").Offset((x-1) * 24).Resize(24)
    ActiveChart.FullSeriesCollection(1).XValues = Worksheets("2022 Occupancy").Range("C2").Offset((x-1) * 24).Resize(24)
    ActiveChart.ChartTitle.Caption = Worksheets("2022 Occupancy").Range("A2").Offset((x-1) * 24).Value         
    ' etc
Next x