Jon

Thanks loads for your help on this - will try this shortly.

Only included a sample originally (it would normally be data for 12
months - just that it wouldn't fit on the page properly within the
google window).

Thanks again for your time on this.

Cheers - Al.

Jon Peltier wrote in message ...
Al -

It's not clear what your six charts are showing. You have six months
data, so it might be one chart per month. But then you say your user
has to add a chart if a new activity is added. Details, whatever.

The thing to do is have your code loop through some counter variable,
generate and position a chart, and assign the values to the series.
Something like:

Sub MakeCharts()
Dim i As Integer
Dim co As ChartObject
Dim ns As Series
Dim rng As Range
Dim ht As Double
Dim wd As Double
ht = 250 ' chart height
wd = 350 ' chart width
Set rng = ActiveSheet.Range(ActiveSheet.Cells(2, 1), _
ActiveSheet.Cells(2, 1).End(xlDown))
For i = 1 To 6
Set co = ActiveSheet.ChartObjects.Add _
(((i - 1) Mod 2) * wd, Int((i - 1) / 2) * ht + 100, wd, ht)
' (left, top, width, height)
Set ns = co.Chart.SeriesCollection.NewSeries
With ns
.Name = "=" & ActiveSheet.Cells(1, 1).Offset(0, i) _
.Address(ReferenceStyle:=xlR1C1, external:=True)
.XValues = rng
.Values = rng.Offset(0, i)
End With
Next
End Sub

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


Al Mackay wrote:

I'm attempting now to try and automate the reports that we generate on
a monthly basis.

We simply record the number of activities by month, and chart each
one.

What I'd like to be able to do is automatically create the charts each
month based on the lines of activites that we have. E.g. if we decide
to add on a new line (another activity) one month the user won't need
to go and create a new chart as this will be automatically generated
when they run the appropriate macro to generate these reports.

Is this possible? I need to ensure that the charts fit two to a
landscape page, 3 down (altogether six charts).

Appreciate any help that you may be able to offer on this - or
alternatively a better? way of generating these stats.

Some example data below:

Title: Jan Feb Mar April May June
Activity 1 137
Activity 2 45 57 117 107 74 105
Activity 3 309

This then needs to be saved in a HTML format? in order that this can
be published to the intranet site ( I can do this part but it still
needs to read the source data for the excel file - is it possible to
save as a static HTML file)???

Cheers all for your help in advance.

Thanks - Al Mackay ( )