Using VBA to swap between a clustered or separated column chart

[Nick_F]

Hi,

I have some data arranged on a worksheet like this...

A B
1 Name Data
2 Bob 8
3 John 12
4 Gary 10

I created the named range "ChartData" which is dynamic to allow
resizing if names and data are added or removed. In the above example
the range would consist of A2:B4

Next I created a chart on the spreadsheet that I want to plot the
named range "ChartData" in so that it is a clustered bar chart with a
legend containing the names.

In VBA I use the following code...
' Activate the chart
ActiveSheet.ChartObjects("Chart 1").Activate
' Add the data to the chart
ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData")
' Give the x axis a name
ActiveChart.SeriesCollection(1).XValues = "={""Scores""}"

My problem is the data always comes out as separated columns and all
the same color (because they are the same series). The way to solve
this is manually right click on the chart, choose select data and then
swap rows/columns. The chart then becomes clustered and each name has
a different colored column and appears in the legend.

How can I alter my code/rearrange my data to get the clustered columns
automatically?

Thanks
Nick

 

[Joel]

ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData"), PlotBy:= _
xlColumns

 

[r]

try

ActiveChart.SetSourceData Source:=Range("Sheet1!ChartData"), PlotBy:=xlRows

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Peter T]

Somehow I suspect you want things as they are but with "vary colors by
point"

Also, is that ChartData named range really dynamic. Why not make real
dynamic ranges for your x-values and series values and do something like
this

I made these names
Names: =OFFSET(Sheet1!$A$2:$A$20,,,COUNTA(Sheet1!$A$2:$A$20))
Data: =OFFSET(Names,,1)

With ActiveChart.SeriesCollection(1)
.XValues = "=Book1!Names"
.Values = "=Book1!Data"
End With

Once you've done that you shouldn't need to do anything again to update your
chart when you add new data.

If you make local/sheet level names change the workbook-name to the
sheet-name

Regards,
Peter T

 

[Nick_F]

Somehow I suspect you want things as they are but with "vary colors by
point"

Thanks, all those suggestions helped.

The Plotby method was not appearing when I recorded a macro and I
forgot that I can use the object browser to look up methods.

Cheers
Nick