Create a chart using VBA with dynamic data

Question

I am writing a VBA function to automatically create a chart from a dataset.

Sub create_graph

     ActiveSheet.Shapes.AddChart.Select
     ActiveChart.ChartType = xlLine
     ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection(1).Values = "='Blad1'!$F$1:$F$13"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Kosten"""    

End sub

This works fine. How I would like to make it more dynamic. So therefore I included:

    Range("B6").Select
    Set x = Range(Selection, Selection.End(xlDown))

So my code is like this now:

Sub create_graph
  ActiveSheet.Shapes.AddChart.Select
  ActiveChart.ChartType = xlLine
  ActiveChart.SeriesCollection.NewSeries

'Nog even kijken hoe ik dit dynamisch kan maken

    Range("B6").Select
    Set x = Range(Selection, Selection.End(xlDown))

   ActiveChart.SeriesCollection(1).Values = x
   ActiveChart.SeriesCollection.NewSeries

End sub

But his throws an error 91.

Any thoughts on what goes wrong?

Please read [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). When you Select another range, you don't have an `ActiveChart` any more. Also please always specify at which line you get the error and the full error message (not everyone knows all the error numbers ;) ). I also highly recommend using `Option Explicit`. — arcadeprecinct, Jul 17 '16 at 16:06
Thanks @arcadeprecinct will keep it in mind. Whats the added values of option explicit? — Frits Verstraten, Jul 17 '16 at 17:27
You will be forced to declare every variable which can prevent accidental type mismatches. It also prevents typos because you will get notified if you use a variable that isn't declared. Also, the VBA editor will have autocomplete for declared objects' methods and properties which can be pretty handy. — arcadeprecinct, Jul 17 '16 at 17:43
@FritsVerstraten did you try the code I gave in my answer below ? — Shai Rado, Jul 17 '16 at 18:04

score 0 · Accepted Answer · answered Jul 17 '16 at 17:18

Try the following code below.

Modify the value of Sheet_Name in the code line to your worksheet name Set ws = ActiveWorkbook.Worksheets("Sheet_Name")

Sub create_graph()

    Dim ws  As Excel.Worksheet
    Dim x As Range

    ' modify Sheet_name to your worksheet name
    Set ws = ActiveWorkbook.Worksheets("Sheet_Name")

    ' Range("B6").Select
    Set x = ws.Range("B6:B" & ws.Cells(ws.Rows.count, "B").End(xlUp).Row)

    ws.Shapes.AddChart.Select
    With ActiveChart
        .ChartType = xlLine
        .SeriesCollection.NewSeries
        .SeriesCollection(1).Values = x
    End With

End Sub

Create a chart using VBA with dynamic data

1 Answers1