Generating dynamic charts with VBA

Question

I have to create almost 200 charts of time series. So I tried to write a macro that finishes most of the work I need to do.
I generated names for the time series like this as an example:

Name:= AKB_ExampleA

The name refers to a dynamic range which I declared with this formula:

=OFFSET('sheet1'!$C$7:$C$137;0;0;COUNT('sheet1'!$C$7:$C$206))

So now to the macro I coded so far:

Sub graphik_erstellen()

Call graphik1("AKB")

End Sub

Sub graphik(Name As String)
'
    Dim Ch As Chart
    Dim RngToCover As Range
    Set Ch = charts.Add
    Set Ch = Ch.Location(Where:=xlLocationAsObject, Name:="Charts")

    With Ch
      .ChartType = xlLine
      .SetSourceData Source:=Range(Name & "_ExampleA")
      .SeriesCollection(1).XValues = Range("Datum_Volumen")
      .SeriesCollection(1).Name = "SERIES1"
      .FullSeriesCollection(1).Select
        With Selection.Format.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(192, 0, 0)
            .Transparency = 0
        End With
      .HasTitle = True
      .ChartTitle.Text = Name & ", Volumen (nach Korrektur)"
      .HasLegend = True
      .Legend.Position = xlLegendPositionBottom
      .Legend.Select
        Selection.Format.TextFrame2.TextRange.Font.Size = 11
        Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
      With .Parent
        .top = 100
        .left = 100
        .height = 287.149606299
        .width = 543.685039370078
        .Name = Name & "_chart"
      End With
End With

End Sub

My problem is, that if I do that, the dynamic range is not really considered. It takes the range of the name (which is $C$7:$C$137) but it should refer to the name itself (in order to be dynamic).
So if I click on the chart to see the series, the series values are declared as: ='sheet1'!$C$7:$C$137 instead of ='sheet1'!ExampleA.

I would be really, really grateful if somebody could help me out. Best Elio

Answer 1

I have rearranged a few lines of code and tried to place comments refering to them as well.

Let me know what works. Youjust might need to change SeriesCollection to FullSeriesCollection. Other than that the code works in my Excel 2010.

The first Sub I just get the Range size according to the data available in Column "C" from Row 7.

Let me know.

Option Explicit

Sub graphik_erstellen()

    'You always want to use direct reference to a sheet/range chart
    'Refering to the WorkBook they are in and the worksheet as well.
    'especially if you are opening multiple WorkBooks / Sheets
    Dim CurrentWorkSheet As Worksheet
    Set CurrentWorkSheet = Workbooks("Book1").Worksheets("Sheet1")

    'Dynamically finding the end of the data in Column C
    Dim LastRow As Long
    LastRow = CurrentWorkSheet.Cells(CurrentWorkSheet.Rows.Count, "C").End(xlUp).Row

    'Setting the range using the document reference aswell
    Dim AKB As Range
    Set AKB = Workbooks("Book1").Worksheets("Sheet1").Range(Cells(7, "C"), Cells(LastRow, "C"))


    Call graphik(AKB)

End Sub

Sub graphik(Name As Range)

    Dim DataChart As Chart
    Dim RngToCover As Range
    Set DataChart = Workbooks("Book1").Charts.Add

    'With Excel 2010 the line above will automatically add the chart as a sheet and not aobject in a sheet
    'Set DataChart = DataChart.Location(Where:=xlLocationAsObject, Name:="Charts")


    With DataChart
        .Name = "Charts" ' This will be the Name of the CHart Tab
        .ChartType = xlLine
        .SetSourceData Source:=Name

        'You can see below I avoided the Select and Selection
        With .SeriesCollection(1)
            'Using Offset I just used the data one cell to the left of the range
            .XValues = Name.Offset(0, -1)
            .Name = "SERIES1"
            With .Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(192, 0, 0)
                .Transparency = 0
            End With
        End With

        .HasTitle = True
        .ChartTitle.Text = "MIDDEL TOP TEXT" 'Name & ", Volumen (nach Korrektur)"
        .HasLegend = True
        With .Legend
            .Position = xlLegendPositionBottom
            .Format.TextFrame2.TextRange.Font.Size = 11
            .Format.TextFrame2.TextRange.Font.Bold = msoTrue
        End With

        'Not sure about this, it doesnt work in my Excel 2010
        '
        With .Parent
            .Top = 100
            .Left = 100
            .Height = 287.149606299
            .Width = 543.685039370078
            .Name = Name & "_chart"
        End With

    End With

End Sub

Let me know what your intention is for the Sheet and Chart names and then I can help with getting that to what you need as well.