ExcelBanter - Efficiently Generate 88 Chartsheets

Hi,

This should get you started. I would try it on a subset of the data
first rather than all 88 sets. You will still need to add any formatting
code you have recorded.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 05/07/2006 by Andy Pope
'
Dim lngRow As Long
Dim lngStartRow As Long
Dim chtDeer As Chart
Dim shtData As Worksheet
Dim rngXData As Range
Dim rngYData As Range
Dim strCounty As String

'
Set shtData = Worksheets("Sheet1")
lngRow = 2
lngStartRow = 2
Do While shtData.Cells(lngRow, 1) < ""
If shtData.Cells(lngRow, 1) < shtData.Cells(lngRow + 1, 1) Then
Set rngXData = shtData.Range( _
"B" & lngStartRow & ":B" & lngRow)
Set rngYData = rngXData.Offset(0, 1)
strCounty = shtData.Cells(lngRow, 1).Value
' make a chart
Set chtDeer = Charts.Add
With chtDeer
Do While .SeriesCollection.Count 0
.SeriesCollection(1).Delete
Loop
.ChartType = xlXYScatterLines
.PlotBy = xlColumns
With .SeriesCollection.NewSeries
.XValues = rngXData
.Values = rngYData
End With
.Location Whe=xlLocationAsNewSheet
.HasTitle = True
.ChartTitle.Characters.Text = strCounty
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
.HasLegend = False
.Name = strCounty
End With
lngStartRow = lngRow + 1
End If
lngRow = lngRow + 1
Loop

Set rngXData = Nothing
Set rngYData = Nothing
Set shtData = Nothing
Set chtDeer = Nothing

End Sub

Cheers
Andy

wrote:
Gang,

I've got a simple X*Y chart with year (1977-present) on the x-axis and
an estimate of deer population size on the y-axis (actually, there may
ultimately be 2 estimates/series). There is nothing really all that
fancy about it. It will be updated each year with the current year's
estimate. The problem is, I need 87 more just like it - 1 for each of
the 88 units that I manage.

I have been reading VBA and Excel books and I have yet to find an
example on how to "mass produce" a bunch of chart sheets. I take that
back - a simple "FOR NEXT" loop will generate the chart sheets. I'm at

a loss as to how to get the chart that I want with the "look" and data
that I need.

An idea thatI've had: Turn the macro recorder on and generate a
prototype chart. Cut and paste this code (after cleaning it up) into a

"FOR NEXT" loop and viola!

Clearly an oversimplification. Any sample code or other guidance would

be appreciated.

BTW the source data for each chart is all in a single worksheet and
looks like the folllowing:

County Year Population
Athens 1977 207
Athens 1978 305
Athens 2006 10,005
Belmont 1977 405
...

Mike

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info