Problems when trying to change source data for pie chart

[Pepen123]

Hi

I have a sheet (excel 2007), where I would like to copy an entire section of the sheet and paste it further down. This section includes a chart, whose sourcedata I would automatically like to change, when running the macro to do the copy/pasting.

If the original data for the Pie chart is in range("B11:C16") and I wish to change this to range("B41:B46"), I would think I could simply do the following:

Charts("ChartName").SetSourceData Source:=Activesheet.Range("B41:C46")

This however gives me an error saying "subscript out of range". Using named ranges will not be necessary (or usefull in this context), I basically just take a section of the worksheet, paste it alittle further down and type in the needed data. Everything but this changing of the source works perfectly, so I am getting alittle frustrated. Hope you can help!

Thanks in advance

Peter

 

[Andrew Poulsom]

Does this work for you?

ChartObjects("ChartName").Chart.SetSourceData Source:=Activesheet.Range("B41:C46")

 

[Pepen123]

Does this work for you?

ChartObjects("ChartName").Chart.SetSourceData Source:=Activesheet.Range("B41:C46")

It certainly seems to do the trick! Thank you very much. My initial approach was just to follow the explanation on msdn, but that was obviousy a mistake

 

[Andrew Poulsom]

I imagine the explanation on MSDN referred to a Chart sheet. If a chart is on a worksheet it's contained in a ChartObject.