A chart macro that can run on multiple sheets

Sorry for what is probably a newbie question, but I have searched around for hours on the web and haven't found a solution.

I have a workbook with a hundred or so sheets. Each sheet has data in cells A1:F5. I'd like to use a macro that will allow me to go to a sheet and create a column chart. The title of the chart is to be read from cell A8.

I've used Excel to record the following macro:

Sub create_chart()
'
' create_chart Macro
'
' Keyboard Shortcut: Ctrl+o
'
Range("A1:F5").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("test!$A$1:$F$5")
ActiveSheet.Shapes("Chart 14").ScaleWidth 1.5266666667, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 14").ScaleHeight 1.5902777778, msoFalse, _
msoScaleFromTopLeft
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
Selection.Caption = "horizontal axis title"
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "vertical axis title"
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = "=test!R8C1"
Selection.Format.TextFrame2.TextRange.Font.Size = 14
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 14").Line.Visible = msoFalse
End Sub




I'm running into a couple problems with this.

One is that it refers specifically to sheet "test", but I need it to apply to whatever sheet I happen to be looking at. That goes for the caption title as well.

The other thing is that it refers to "Chart 14" (you can tell that I've been trying this multiple times, creating and deleting charts as I go along). When I try running it on a different sheet, I get an error message that the item with the specified name wasn't found, and debug shows that it's referring to the chart name.

How do I generalize the macro above so that it's not tied to a specific sheet name or chart number?

Thanks much for any pointers.

Thanks for the quick response, but I'm not sure I understand it.

I'm just trying to create a macro that quickly charts the data in a sheet. I have about 50 sheets with the same data layout so the macro would be useful for creating a separate chart in each sheet.

Whether the data are in 50 sheets or in one sheet, I still have separate 50 charts to create.

Not sure what I'm missing here?

An example is attached. There are 3 sheets, each with different data
that correspond to different cases. Each case has a different chart
associated with it. The charts will be used in a separate presentation
(one presentation with 50-100 charts in it).

I just want a methodology to create the charts quickly with the
consistent sizing and formatting that I want. In the time
that I have spent researching this and fooling around with the
VBA script, I'm sure I could have done the whole thing manually.
But it will probably come up again in the future.

Thanks.

Andy, thank you very much, it works perfectly!

I just have one followup question. How do I modify CreateChart() to allow
me to re-run it on a single sheet. I had a couple sheets where the data
layout was not quite the same as on other sheets, and this caused problems
for the chart generation. I'd just like to re-run it on those individual sheets.

Thank you!

Never mind, I figured it out, I think.

Thanks again for the help.