Automating the creation of a chart per sheet

[rbrychckn]

I'm new to charts and I have a question on how one could create a char
for the same range of cells (BB27:BB36) for a number of sheets.

That is, can I tell excel, "Create a chart for every sheet in this fil
that pulls the data from bB27:BB36 on THAT particular sheet"? Righ
now, if I just cut and paste the chart, it will still pull data fro
the old sheet. Short of manually creating a chart for every sheet, ca
you somehow automate this process like a formula?

Thank

 

[Jon Peltier]

Press Alt-F11 to open the VB Editor. Make sure the workbook is
highlighted in the Project Explorer list, then choose Module form the
Insert menu. Copy the code below into the code module that pops up. Back
in Excel, press Alt-F8 to bring up the Macro dialog, and run
OneChartPerSheet.

Sub OneChartPerSheet()
Dim ws As Worksheet
Dim co As ChartObject
For Each ws In ActiveWorkbook.Worksheets
With ws.Range("BB8:BI26")
'' range for chart to cover
Set co = ws.ChartObjects.Add _
(.Left, .Top, .Width, .Height)
co.Chart.SetSourceData _
Source:=ws.Range("BB27:BB36")
End With
Next
End Sub

- Jon