Solved: Chart needs data points transposed

[JimS]

I’m trying to chart some data points that I receive from another source but need to change how the data is laid out for the chart to work.
Is there a way that a Macro could transpose the data so that the data points stay grouped by the individual chart factors?
Or is there a way that the chart can deal with how the data is laid out (see the attachment for details).
Thanks…
JimS

[someboddy]

Rightclick on the chart and then click on "Select Data..." to open the data selection dialog. There there is a button labeled "Switch Row/Column" that does exactly what you want.

[JimS]

I tried that - it does not look like the chart on the "End Result" worksheet, which is what I'm after.

[someboddy]

Oh, didn't notice that sheet. I see now what you mean - the tool you need is a pivot chart. The first button in the "Insert" tab in the ribbon is labeled "PivotTable". It has a down arrow below it - click on it and select "PivotChart". Then you get to select the data(should autoselect if for you if you were already on it) and where to place the new pivot chart. After that dialog, you get to select the fields for your pivot chart - drag "Chart Factors" to "Axis Fields", "Quarter" to "Legend Fields", and "Chart Values" to "Values"(make sure its turned into a sum - if Excel decides it wants to count it, left click on the field, click on "Value Field Settings" and change it to sum.

After you do that, you can play with the formatting to make it look good.

[mdmackillop]

Basically a recorded macro
[VBA]Sub Macro3()
Dim ws As Worksheet
Set ws = Sheets.Add

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Original Data!R1C1:R15C4", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=ws.Name & "!R1C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
ws.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range(ws.Name & "!$A$1:$C$18")
ws.Shapes("Chart 1").Top = 100
ws.Shapes("Chart 1").Left = 100
With ws.PivotTables("PivotTable1").PivotFields("Quarter")
.Orientation = xlRowField
.Position = 1
End With
With ws.PivotTables("PivotTable1").PivotFields("Quarter")
.Orientation = xlColumnField
.Position = 1
End With
With ws.PivotTables("PivotTable1").PivotFields("Chart Factors")
.Orientation = xlRowField
.Position = 1
End With
ws.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Chart Values"), "Sum of Chart Values", xlSum
End Sub
[/VBA]

[JimS]

someboddy,
Thanks - I thought about using a Pivot Table Chart in the middle of the night last night.

mdmackillop,

The 1st line (ActiveWorkbook.PivotCaches.Create....) fails with an "Invalid procedure call or arguement" error.

Any idea why?

Thanks for your help as well...

JimS

[mdmackillop]

I retested on your posted sample with no issues. Try recording your own macro to identify any differences.