Excel VBA Chart Automation Run Time Error

[BrianRowe]

Hi,
I have written some VBA code (Excel 2003) that copies the 2nd worksheet containing 3 charts and updates them with data on the 1st worksheet. This iterative process works fine if less than 26 graphs are created, at which point it falls over. This issue is recognised by Microsoft, and they suggest adding a new sheet using an external template to avoid this problem. However this gives run time error 2147221080.... The section of code is as follows:

If sheet_num > 2 Then
'Sheets(sheet_num - 1).Select
'Sheets(sheet_num - 1).Copy After:=Worksheets(Worksheets.Count)
Sheets.Add Type:=total_path & "\Q60 Graphs Template.xls", After:=Worksheets(Worksheets.Count)
Sheets(sheet_num).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select

The commented out lines (2 & 3) are the old code that works fine for 26 iterations. Line 4 is the new code. The error occurs on the final line: ActiveChart.PlotArea.Select

It appears to me that whilst "Chart 1" is being activated, it is not being recognised as the active chart ??

Any suggestions gratefully received.......

 

[SkipVought]

Hi,

This is not so much a solution to your immediate problem as it is an editorial on chart proliferation.

I am an advocate of Control Objects for Charts, to control the display. For instance, we have scores of machines on our shop floor. So do you make a workbook with scores of sheets/charts, or a workbook with ONE chart sheet (along with whatever supporting data sheet(s) are necessary) to show loads?

On the Chart Sheet, I might have a drop down boxe to select a machine. I might make it even more function by have other controls to group machines with similar characteristics.

ONE CHART, Many Views!

Also, on a Chart Sheet, you can display multiple charts to represent different kinds of data: load, Piece Counts, Net Inventory/Demand/Supply, etc.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}