Solved: Changing Range of sources for chart

[AnswerSeeker]

Hello, I have a chart in Worksheets(2) which I like to update after finishing the calculations. The new data is added as new row below the previous data. The source is in another worksheet called "Statistics" and the chart in Worksheet(2).

my idea so far:

Sub Chart()
Dim FinalRow As Integer
FinalRow = Worksheets("Statistics").Range("A3").Value + 2

Worksheets(2).Activate
Worksheets(2).ChartObjects("MyStatistics").Activate
With ActiveChart
.SeriesCollection(1).SetSourceData Source:=Range(Worksheets("Statistics").Range("B3"), Worksheets("Statistics").Range("B" & FinalRow))
.SeriesCollection(2).SetSourceData Source:=Range(Worksheets("Statistics").Range("D3"), Worksheets("Statistics").Range("D" & FinalRow))
End With
End Sub

[mancubus]

hi.
just define a dynamic range and set this range as chart's data source...

assuming A1 is topleft cell of chart range:
=OFFSET(Statistics!$A$1,0,0,COUNTA(Statistics!$A:$A),COUNTA(Statistics!$1:$ 1))

[AnswerSeeker]

Thx, I have not known this function before.

I followed also some other tutorials, but I always get the problem that my source is not dynamic. In source data of the chart I typed in (e.g.) "Statistics!myData". "Statistics is the worksheet name and "myData" the name of the range. "myData"'s formula is: =OFFSET(Statistics!$B$3,0,0,COUNTA(Statistics!$B:$B)-2,1) (--> to get sources for one series)

After typing it, is up-to-date, but after adding new rows the source refernce always change to an absolute one like: =Statistics!$B$3:$B$14

[AnswerSeeker]

Okay, I could figure out how it works. Problem solved :)