Automatically create charts from data table - help needed

I need t create a large number of charts in Excel 97 from a table of data,
which is a very long-winded process to do manually. I have a table of data
which consists of a header row with category data for the x axis, a column
containing file references, from which the chart title is created, and
dozens of rows of data. To make matters worse, I have 35 of these tables! I
need to create a chart for each row of data as I need to print these out to
include in a series of bound reports. The charts need to be identical so the
same category data applies to each one. What I have been doing in the past
is to create one chart in the format that I want then copy it and change the
source data. Ok for a couple of charts, but not acceptable when I have
hundreds to do.

Unfortunately, I am not very familiar with VBA and I cannot work out a macro
to do this. I am sure someone else has had exactly the same problem and
there's a solution out there somewhere! Can anyone help, please?

Gordon

Gordon -

A macro would go something like this:

Sub MakeManyCharts()
Dim rngCat As Range
Dim Cht As Chart
Dim Srs As Series
Dim i As Integer
Dim iMax As Integer
Dim sTitle As String

With ActiveSheet
' Define range containing categories
Set rngCat = .Range(.Cells(1, 2), .Cells(1, 2).End(xlToRight))
' Create the chart
Set Cht = .ChartObjects.Add(100, 100, 375, 250).Chart
' Count rows to be charted
iMax = .Cells(2, 1).End(xlDown).Row
End With
With Cht
' Set up chart as appropriate
.ChartType = xlLineMarkers
Set Srs = .SeriesCollection.NewSeries
Srs.XValues = rngCat
.HasTitle = True
End With
i = 0
With Cht
' Loop through the rows
For i = 1 To iMax - 1
' Get the title
sTitle = rngCat.Offset(i, -1).Resize(1, 1).Value
If Len(sTitle) 0 Then
' Only process rows with a title
' Change the title
.ChartTitle.Text = sTitle
' change the values
Srs.Values = rngCat.Offset(i, 0)
' change to .PrintOut,
' or copy chart and paste elsewhere
' or export it, or whatever
.PrintPreview
End If
Next
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______


Gordon wrote:

I need t create a large number of charts in Excel 97 from a table of data,
which is a very long-winded process to do manually. I have a table of data
which consists of a header row with category data for the x axis, a column
containing file references, from which the chart title is created, and
dozens of rows of data. To make matters worse, I have 35 of these tables! I
need to create a chart for each row of data as I need to print these out to
include in a series of bound reports. The charts need to be identical so the
same category data applies to each one. What I have been doing in the past
is to create one chart in the format that I want then copy it and change the
source data. Ok for a couple of charts, but not acceptable when I have
hundreds to do.

Unfortunately, I am not very familiar with VBA and I cannot work out a macro
to do this. I am sure someone else has had exactly the same problem and
there's a solution out there somewhere! Can anyone help, please?

Gordon