On Jul 12, 8:43*am, Jeffrey Marks wrote:
Jim
Thank you! This works great. I have to do this for 100+ charts and hoped to create 1 macro that does this for each row. Row 1 has the labels in the previous example and Row 2 has the data. I would need a counter to increase by 1 (and not include Row 2 in Row 3's chart, etc.) In preparation for this, I also put the ChartTitle in Column D
*I've been trying to research a way to do this for bar charts on-line, but haven't found anything applicable. I know I need a loop and a counter, but not sure of where to go to find an example of cluster columns.
Jeff
Sub OATChartCreate()
*Dim chtNew As Chart * * * * * * * '<<<
* * ActiveCell.Resize(2.6).Select
* * ActiveSheet.Shapes.AddChart.Select
* * Set chtNew = ActiveChart * * '<<<
* * chtNew.SetSourceData Source:=Range("'OAT Test Charts Data_Crosstab'!$F$1:$K$2")
* * chtNew.ChartType = xlColumnClustered
* * chtNew.Legend.Delete
* * chtNew.HasAxis(xlValue) = True
* * chtNew.Axes(xlValue).MinimumScale = 0
* * chtNew.Axes(xlValue).MaximumScale = 1
* * chtNew.Axes(xlValue).MajorUnit = 0.1
* * chtNew.Axes(xlValue).MajorUnit = 0.2
* * chtNew.Axes(xlValue).TickLabels.NumberFormat = "0.00%"
* * chtNew.Axes(xlValue).TickLabels.NumberFormat = "0%"
* * ActiveChart.ChartArea.Select
* * chtNew.SetElement (msoElementPrimaryValueAxisTitleVertical)
* * chtNew.SetElement (msoElementChartTitleAboveChart)
* * chtNew.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
* * chtNew.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent Passing"
End Sub
Take a look at the Chart Assistant,
http://www.add-ins.com/chart_assistant.htm.
It is designed to create one chart per row of data and allows you to
customize the charts.
Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on
VB macros for Excel