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,

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.

--
Regards,
Tom Ogilvy

"Money" wrote in message
...
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,

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 Befo=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
-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Money" wrote in
message
...
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,


.

Not that I am aware of.

--
Regards,
Tom Ogilvy

wrote in message
...
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 Befo=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
-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Money" wrote in
message
...
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,


.

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


wrote in message
...
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 Befo=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
-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Money" wrote in
message
...
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,


.

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,

-----Original Message-----
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


wrote in message
...
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 Befo=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
-----Original Message-----
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.

--
Regards,
Tom Ogilvy

"Money" wrote in
message
.. .
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,


.



.