copying graph template error

I've found many posts on this but mine is a bit more complicated. I have a list of stuff to graph on one sheet and a graph template on another. A macro copies the template and puts it into a new sheet for each thing in the list. I get an error after a certain amount of sheets are copied. I read that this occurs because excel is dumb. The microsoft help site says to save the document, close it, and reopen it in the chart creation loop. This is simple to do, however....the document that is being saved, closed, repopened calls to open another workbook when it's openned. This other workbook is the one that runs all the macros! Including the chart creation one...Is there another way around this problem?

Hello jeffziggy,

I've found many posts on this but mine is a bit more complicated. I have a list of stuff to graph on one sheet and a graph template on another. A macro copies the template and puts it into a new sheet for each thing in the list. I get an error after a certain amount of sheets are copied. I read that this occurs because excel is dumb. The microsoft help site says to save the document, close it, and reopen it in the chart creation loop. This is simple to do, however....the document that is being saved, closed, repopened calls to open another workbook when it's openned. This other workbook is the one that runs all the macros! Including the chart creation one...Is there another way around this problem?

Please can you post the relevant parts of your code (including variable declarations) together with full details about the error you are experiencing - the actual line, error number and error message. It would also be helpful if you could give us the link to the microsoft help page you are referring to.
I read that this occurs because excel is dumb.
Is that what it says on the Microsoft help site?

Colin

http://support.microsoft.com/kb/210684



Dim Nsheets as integer
Dim x as long
Nsheets = Sheets.count
For x = 8 To 90000
If Worksheets("Matrix").Range("F" + CStr(x)).Value <> "" Then
Sheets(Nsheets).Select
Sheets(Nsheets).Copy after:=Sheets(Nsheets)
Sheets(Nsheets + 1).Select
Sheets(Nsheets + 1).Name = Worksheets("Matrix").Range("F"+ CStr(x)).Value
ActiveChart.ChartTitle.Select
Selection.Characters.Text = Worksheets("Matrix").Range("F" + CStr(x)).Value
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Worksheets("Matrix").Range("G" + CStr(x)).Value
Nsheets = Sheets.count
End if
Next

after some charts are copied, i get the error "1004 copy method of charts failed" on this line:


Sheets(Nsheets).Copy after:=Sheets(Nsheets)

For x = 8 To 90000
Presumably you're using Excel 2007 then?

So let me make sure I read this right. If that range is full, your intention is to have 89,993 (including the template) charts in your workbook, each as a separate chart sheet?

If Worksheets("Matrix").Range("F" + CStr(x)).Value <> "" Then

The worksheet "Matrix" has the list of the stuff to be graphed. So it checks each time if the cell has something in it. I guess it would be better to use a Do While statement but I'm sure thats not the problem.

The suggested workaround seems applicable to your situation, doesn't it?

WORKAROUND
To work around this problem, insert a new worksheet from a template instead of copying an existing worksheet. To do this, follow these steps, as appropriate for the version of Excel that you are running.

Ya it worked. Thanks for pointing that out, I didn't see that last part. Is there a way of finding out the excel version you're using with VBA? I'll have to make two templates and depending on the excel version you're using it will either get the xlt file or the xltx one. Unless xlt will work on 2007?

Try looking up "Version" in the Help. ;)