Change pie chart source data by vba

[mohamed gani]

Hi all,

I wanna change the source of pie chart by using vba, i tried this code but it select the colum C also. I just want to select a range (column B & Column D) only. below is a vba code,

ActiveSheet.ChartObjects("Chart 1").Activate
 ActiveChart.ChartArea.Select
 ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("b7:b10", "d6:d10")

Here is a table, I just want to ignore the column C, while select source data.

[TABLE="width: 241"]
<tbody>[TR]
[TD]
[TABLE="width: 197"]
<tbody>[TR]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]Group
[/TD]
[TD]amount
[/TD]
[TD]% Total
[/TD]
[/TR]
[TR]
[TD]abc
[/TD]
[TD]100
[/TD]
[TD]54.0 %
[/TD]
[/TR]
[TR]
[TD]def
[/TD]
[TD]200
[/TD]
[TD]62.0 %
[/TD]
[/TR]
[TR]
[TD]ghi
[/TD]
[TD]300
[/TD]
[TD]14.0 %
[/TD]
[/TR]
[TR]
[TD]jkl
[/TD]
[TD]500
[/TD]
[TD]21.0 %
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance,

by ,
Gani

 

[pgc01]

Hi Gani

Try:

ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData _
    Source:=Range("Sheet2!b6:b10,Sheet2!d6:d10")

Remark: not a fan of a chart called "Chart 1"

 

[mohamed gani]

Hi,

Its works good. Thanks a lot.

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[mohamed gani]

Hi ,
Same code but sheet name is different, its not working. Any idea ?

ActiveSheet.ChartObjects("Chart 39").Chart.SetSourceData Source:=Range("Sold by Purchase Group!c10:c28,Sold by Purchase Group!e10:e28")

Thanks

 

[pgc01]

Hi ,
Same code but sheet name is different, its not working. Any idea ?

ActiveSheet.ChartObjects("Chart 39").Chart.SetSourceData Source:=Range("Sold by Purchase Group!c10:c28,Sold by Purchase Group!e10:e28")

Thanks

Hi
Welcome to the board

When you refer to a sheet name with special characters like a space you must enclose the name in single quotes.

For ex.: Sheet1!A1 but 'Sheet 1'!A1

In your case, try:

ActiveSheet.ChartObjects("Chart 39").Chart.SetSourceData  Source:=Range("[COLOR=#ff0000]'[/COLOR]Sold by Purchase Group[COLOR=#ff0000]'[/COLOR]!c10:c28,[COLOR=#ff0000]'[/COLOR]Sold by Purchase  Group[COLOR=#ff0000]'[/COLOR]!e10:e28")

 

[mohamed gani]

Hi PGC,

Really great, Thanks Again.

 

[pgc01]

I'm glad it's working ok. Cheers!