I have the below line in a macro and it updates the series range of a chart as expected:
Sheets("Sheet1").ChartObjects("Chart 1").Chart.SetSourceData Source:=Sheets("Sheet1").Range("A6:D21")
However, if I amend it to the below to have the chart update from a named range, I get an error (Run-time error 1004 - Application-defined or object-defined error):
Sheets("Sheet1").ChartObjects("Chart 1").Chart.SetSourceData Source:=Sheets("Sheet1").Range("GraphDataX")
For info, the named range is dynamic and if defined using the below:
Formula: copy to clipboard 
=Sheet1!$A$6:INDEX(Sheet1!$D:$D,COUNTA(Sheet1!$D:$D)+5)


If the named range referrs to a static range (i.e. A6:D21) then the second line works fine so I'm guessing it's all to do with the dynamic aspect.

I need it to be a dynamic range as an earlier part of the macro adds/removes rows and data from the range that drives the chart.

Any ideas to get around this?