OK, this turned into an hellacious mini-project, the like of which I'll never do again, but it was educational so I've got no hard feelings.
I'm using XL2000 on a 800MHz PC running NT4 SP6 (I think).
The workbook I was given contained a data sheet and a sample chart that was embedded on a separate sheet.
My intention was to copy the sample chart and simply change the data series each time to create a new chart.
This worked great until I got to chart 70. I was then getting "1004" errors. A quick look in the MSKB produced this little gem:
http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q210684
What this meant was that I had to move my code into a different workbook so that I could save and close the data workbook after a certain number of charts had been created. (this is the only workaround MS offered to get rid of the "1004" error.
The code then worked great, until I hit chart 100 and got an "Automation Error". It turned out I'd hit the fonts limit as described by this joyous article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q168650
So I got rid of Autoscale and made all the fonts on the chart the same. (it's strange that I got this error since I'm using XL2000 and this is apparently an XL97 only bug)
Finally after many opening and closings of Excel I got the code to create 124 charts on 124 worksheets. Writing this explanation has now taken me into the 3 hour mark for solving the problem. Yes I am an idiot, yes I should get fired, but this looked like work.
Anyway, here is the code that I finally came up with, I'm posting it here for posterity:
<pre>
Option Explicit
Public Sub CreateCharts()
'Declare Variables
Dim oBook As Workbook
Dim sFullPath As String
Dim DataSht As Worksheet
Dim TemplateSht As Worksheet
Dim sht As Worksheet
Dim cht As Chart
Dim sQuestion As String
Dim sNameFirstPart As String
Dim sNameSecondPart As String
Dim sSeries1Values As String
Dim sSeries2Values As String
Dim iFirstRow As Integer
Dim iLastRow As Integer
Dim iFirstCol As Integer
Dim iLastCol As Integer
Dim iRow As Integer
Dim iCol As Integer
Dim iCount As Integer
'Turn off screen updates to speed up code
Application.ScreenUpdating = False
' Initialise Variables
sFullPath = "C:TempCrazySpreadsheet.xls" 'This is where we will save our workbook.
Set oBook = Workbooks("CrazySpreadsheet.xls") 'Set the workbook to the data workbook.
Set DataSht = oBook.Sheets("Inpt.Data") 'The sheet with the data
Set TemplateSht = oBook.Sheets("TEMPLATE") 'The template sheet we're copying
iFirstCol = 2 'First column with data
iLastCol = 8 'Last column with data
iFirstRow = 4 'First row with data
iLastRow = DataSht.Range("A65536").End(xlUp).Row 'Last row with data. This is picked up automatically
For iCol = iFirstCol To iLastCol
sNameFirstPart = DataSht.Cells(iFirstRow - 2, iCol).Value 'First part of the sheet name, i.e. the Unit name
For iRow = iFirstRow To iLastRow Step 4
sQuestion = DataSht.Cells((iRow - 1), 2).Value 'The question for the next sheet
sNameSecondPart = "(" & Left(sQuestion, (InStr(1, sQuestion, ":") - 1)) & ")" 'Lastpart of the sheetname i.e. the question number
'Copy Template
TemplateSht.Copy After:=oBook.Sheets.Item(oBook.Sheets.Count)
Set sht = Sheets(Sheets.Count)
Set cht = sht.ChartObjects(1).Chart
'Increment counter
iCount = iCount + 1
'Rename sheet
sht.Name = sNameFirstPart & sNameSecondPart
'Add unit name and question to the sheet with chart
sht.Range("C3").Value = sNameFirstPart
sht.Range("B5").Value = sht.Range("B5").Value & " " & sQuestion
'Set new data ranges
sSeries1Values = "=(Inpt.Data!R" & iRow & "C" & iCol & ",Inpt.Data!R" & iRow & "C9:R" & iRow & "C10)"
sSeries2Values = "=(Inpt.Data!R" & (iRow + 1) & "C" & iCol & ",Inpt.Data!R" & (iRow + 1) & "C9:R" & (iRow + 1) & "C10)"
'Change old data ranges
With cht
.SeriesCollection(1).Values = sSeries1Values
.SeriesCollection(2).Values = sSeries2Values
End With
'Save and close book because of Excel limitation with copying sheets
If iCount Mod 20 = 0 Then
oBook.Close SaveChanges:=True
Set oBook = Nothing
Set oBook = Application.Workbooks.Open(sFullPath)
Set DataSht = oBook.Sheets("Inpt.Data") 'The sheet with the data
Set TemplateSht = oBook.Sheets("TEMPLATE") 'The template sheet we're copying
End If
Next
Next
End Sub</pre>
That's all I have to say about that.
Thank you.
Good night.
(Disclaimer, this is probably not the best way to solve this problem, if I had more time I'd have created the charts programmatically.)