Excel VBA error

[Money]

I have the need to programmatically create up to 100
worksheets in a workbook. I have run into a limitation
that hopefully has an answer. After about 20 to 30 copy
before comands, if bombs. I can save my changes exit
excel reopen and continue to add worksheets. It appears
there is a cache or memory buffer that i will have to
clear after 20 worksheet copies before continuing but have
been unsucessful thus far. Help.

Thanks,

 

[Tom Ogilvy]

Try copying one, then two, then four, then eight, then 16, then 32, then 36

I understand it is the number of copy actions rather then the number of
sheets that can be problematic.

 

[Guest]

Thought of that, but it introduces more problems, as each
sheet i create, i have to perform an operation on before
going to the next -- here is a snippit
Dim Val As Variant

''''''''''create sheets based on contract list from
template'''

Sheets("DATA").Select
Range("A5").Select
Do Until ActiveCell = ""
Val = ActiveCell.Value
Sheets("Template").Copy Before:=Sheets(2)
Sheets("Template (2)").Select
ActiveSheet.Name = Val
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
Application.CutCopyMode = False
Selection.Characters.Text = "G0" & Val
Selection.AutoScaleFont = False
Range("A1").Select
ActiveCell = Val
Sheets("DATA").Select
ActiveCell.Offset(Rowoffset:=1).Activate

Loop

Is there not a way to clear the memory other than closing
excel?? -- Plese help if u can.

Thanks

 

[Tom Ogilvy]

Not that I am aware of.

--
Regards,
Tom Ogilvy

Thought of that, but it introduces more problems, as each
sheet i create, i have to perform an operation on before
going to the next -- here is a snippit
Dim Val As Variant

''''''''''create sheets based on contract list from
template'''

Sheets("DATA").Select
Range("A5").Select
Do Until ActiveCell = ""
Val = ActiveCell.Value
Sheets("Template").Copy Before:=Sheets(2)
Sheets("Template (2)").Select
ActiveSheet.Name = Val
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
Application.CutCopyMode = False
Selection.Characters.Text = "G0" & Val
Selection.AutoScaleFont = False
Range("A1").Select
ActiveCell = Val
Sheets("DATA").Select
ActiveCell.Offset(Rowoffset:=1).Activate

Loop

Is there not a way to clear the memory other than closing
excel?? -- Plese help if u can.

Thanks

 

[Trevor Shuttleworth]

You don't say what version of Excel you are using ... could that be a
factor?

I've just created 70+ sheets using your code in Excel 2003 though I can't
replicate the chart element. Perhaps you could temporarily remove the
additional code to check if you can just create the sheets. Then loop
through and process the additional code.

It might take a little longer but it might run continuously.

Regards

Trevor

 

[Money]

I'm using 2000 on a corporate machine, - I will try and
upgrade through IT wish me luck. Thanks for time and
hopefully an upgraded excel will address my problem

Thanks,