Search code examples
excelvba

Excel VBA variable Chart Range

I have a datatable were i add every week a column and i tryed to write macro to change the chart source. 

Sub test()
Dim ges, koz, daz  As Range
Dim Diaz As Integer

Sheets("Autopilot").Select
Diaz = Range("I2").Value  (the value will change every week like I2 = I2 +1)
Set koz = Range("C3").Resize(, Diaz)   ( this is the Header row)
Set daz = Range("C772").Resize(2, Diaz)  (these are the 2 data rows for the chart) 
Set ges = Union(koz, daz)
Sheets("Diagramm").Select
   ActiveChart.ChartArea.Select
   ActiveChart.SetSourceData Source:=Sheets("Daten").Range(ges)
End Sub

the last row doesn't work. i tried it without the Range at the end (only .ges).

anyone has an idea how this would work?

best regards

Solution

  • I found my own solution:

    With Sheets("Daten")
ActiveChart.SetSourceData Source:=ges
End With