Dynamic chart with VBA

[HV_L]

Hi,
First post here, so please be gentle :think:
I have a chart which I want to create with VBA, because I'm using a template om which new (to be created sheets) are based.
I have this code now but I can't get the values properly assigned, who can help me out?? .. Thanks
Sub AddChart()

ActiveSheet.ChartObjects.Add(25, 1350, 301.5, 155.25).Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=ActiveSheet.Range("B85"), PlotBy:=xlColumns


ActiveWorkbook.Names.Add Name:="Jaar", RefersTo:= _
"=OFFSET(Basisgegevens!$G$2,COUNTA(Basisgegevens!$G$2:$G26)-1,0,-MIN(Basisgegevens!$K$2,COUNTA(Basisgegevens!$G$2:$G$22)-1),1)"
ActiveWorkbook.Names.Add Name:="Omzet", RefersTo:="Offset(Basisgegevens!Jaar, 0, 1)"
ActiveWorkbook.Names.Add Name:="Resultaat", RefersTo:="Offset(Basisgevens!Jaar, 0, 2)"

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "=Basisgegevens!Omzet"
ActiveChart.SeriesCollection(1).Name = "=""Omzet"""
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Basisgegevens!Jaar"
ActiveChart.SeriesCollection(2).Values = "=Basisgegevens!Resultaat"
ActiveChart.SeriesCollection(2).Name = "=""Resultaat"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="2008"
End Sub

[p45cal]

1. Just a questiom whether the highlighted addresses are as intended (should they be the same?)
ActiveWorkbook.Names.Add Name:="Jaar", RefersTo:= _
"=OFFSET(Basisgegevens!$G$2,COUNTA(Basisgegevens!$G$2:$G26)-1,0,-MIN(Basisgegevens!$K$2,COUNTA(Basisgegevens!$G$2:$G$22)-1),1)" 2. amend this from:
ActiveWorkbook.Names.Add Name:="Omzet", RefersTo:="Offset(Basisgegevens!Jaar, 0, 1)"
to:
ActiveWorkbook.Names.Add Name:="Omzet", RefersTo:="=Offset(Basisgegevens!Jaar, 0, 1)"

3. Amend this:
ActiveWorkbook.Names.Add Name:="Resultaat", RefersTo:="Offset(Basisgevens!Jaar, 0, 2)" to:
ActiveWorkbook.Names.Add Name:="Resultaat", RefersTo:="=Offset(Basisgegevens!Jaar, 0, 2)"
Finally, take a look at the code below, which I think does the same thing, but in a way that makes it easier to see what you're doing when it needs adjusting.
Sub AddChart()
ActiveWorkbook.Names.Add Name:="Jaar", _
RefersTo:="=OFFSET(Basisgegevens!$G$2,COUNTA(Basisgegevens!$G$2:$G26)-1,0,-MIN(Basisgegevens!$K$2,COUNTA(Basisgegevens!$G$2:$G$22)-1),1)"
ActiveWorkbook.Names.Add Name:="Omzet", RefersTo:="=Offset(Basisgegevens!Jaar, 0, 1)"
ActiveWorkbook.Names.Add Name:="Resultaat", RefersTo:="=Offset(Basisgegevens!Jaar, 0, 2)"
With ActiveSheet.ChartObjects.Add(25, 1350, 301.5, 155.25).Chart
'.SetSourceData Source:=ActiveSheet.Range("B85"), PlotBy:=xlColumns 'you probably don't need this line
.ChartType = xlColumnClustered
With .SeriesCollection.NewSeries
.Values = "=Basisgegevens!Omzet"
.Name = "=""Omzet"""
.XValues = "=Basisgegevens!Jaar"
End With
With .SeriesCollection.NewSeries
.Values = "=Basisgegevens!Resultaat"
.Name = "=""Resultaat"""
End With
.Location Where:=xlLocationAsObject, Name:="2008"
End With
End Sub

[HV_L]

Hi p45cal,

Your code is indeed better readable and working as a charm!!:thumb
Thanks for helping me out, great !
:beerchug: