Using VBA to swap between a clustered or separated column chart
 

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