Repeated calls of Chart.SetSourceData give error 1004
This doesn't address why the error occurs. This is a workaround.
Before calling SetSourceData, delete all the existing series currently in the chart, and the code will run as expected.
For j = cht.Chart.SeriesCollection.Count To 1 Step -1 cht.Chart.SeriesCollection(j).DeleteNext jI'm not sure why the error occurs in the first place, but this makes it go away.
Another possibility is to define a named range for the data that's defined using the Offset formula and appropriate reference cells. This requires the data to be contiguous, not change the initial row & column it begins in, and for you to setup at least one reference formula (=COUNTA() on the column/row containing the data) that can be used to set the height/width of the offset range.
Otherwise a very handy little work around to take this out of macros and put it in worksheet logic.