help with chart

[Gary Keramidas]

can someone please help me out.

i have a chart on a page that uses the range c4:e16. it defaults to plot by
rows. i am just looking for a simple macro to change it to plot by columns.
can i even do this?

i tried the following, but get an error 9, subscript out of range. i right
clicked the chart and chose assign macro, just to find the chart number, and
it tells me it is chart4, so that's what i tried.

Sub Change_Chart()
Sheets("Monthly Totals").Select
Charts(4).SetSourceData Source:=Sheets("Monthly Totals").Range("c4:e16"), _
PlotBy:=xlcolumns

End Sub

 

[Guest]

What kind of chart are you using (column, line, pie, xy, ...)?

 

[Gary Keramidas]

it's a column chart. if i right click it, choose source data i can click by
rows and it changes, so i thought there would be an easy way to do that with
code.

 

[Guest]

I am guessing that the chart is embedded on a sheet (drawn inside a box)
rahter than a chartsheet (whole worksheet is chart)? If so, the problem
would be in Charts(4): Charts only counts chartsheets. Instead, use
Worksheets("Monthly Totals").ChartObjects(n).Chart.SetSourceData...
(n above is an index number find it like you found he sugar packets.

 

[Gary Keramidas]

got it thanks. i had to use a 1 for n in your example. worked like a charm

thanks

 

[Jon Peltier]

Hi Gary -

I know you got it, but here's something even easier you can use next time:

ActiveChart.PlotBy = xlRows
ActiveChart.PlotBy = xlColumns

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______