Option Explicit
Public Sub DiagrammQuelleAendern()
Const maxRows As Long = 20
Dim oBlatt As Worksheet, oDia As ChartObject
Set oBlatt = Worksheets("Tabelle1")
Dim myRows As Long
With oBlatt
myRows = WorksheetFunction.Max( _
.Cells(Rows.Count, 3).End(xlUp).Row - maxRows, 1)
Set oDia = Sheets("Tabelle2").ChartObjects(1)
oDia.Chart.SetSourceData .Range("B" & myRows, _
"F" & .Cells(Rows.Count, 3).End(xlUp).Row)
End With
End Sub