I am struggled with a way to update my charts. This is how I would like it is working.
Chart data is located in a row, so evry day new column with data is added. I have multiple charts each of them has several series. I would like a macro to extract formula of series collection, which is, for example SERIES(Sheet1!$A$11:$C$11,Sheet1!$E$1:$EN$1,Sheet1!$E$11:$EN$11,1) and replace column EN (can be also a different column) by last column in the row with a help of End(xlToRight)). So that formula becomes (Sheet1!$A$11:$C$11,Sheet1!$E$1:$EZ$1,Sheet1!$E$11:$EZ$11,1) .
Dim m As ChartObject
Dim n As ChartObject
For m =1 to 6
For n = 1 To Sheet1.ChartObjects(m).Chart.SeriesCollection.Count
Sheet1.ChartObjects(m).Chart.SeriesCollection(n).Formula = ....
!!! Please do not offer dynamical named ranges based on offset function, because I have several sheets with multiple graphs each consisting of various series. Time killing to put in all named series.