Addings sheets using Macro

[rob]

I have created a macro to add a number of sheets.
But every time when I want to add the 14th sheet, I receive a 1004 error.
The macro is very simple:
for i = 1 to 20
Sheets("Default").Copy before:=Sheets(2)
next i

In order to do so, I copy a template sheet.

What is wrong?
It looks like a stack overflow?
Do I have to reset some parameters?

I cannot even add a sheet manually after this error.
When I save and re-open I can again add sheets upto 14.
So it is not the total number of sheets that gives me the problem.

Any help?

 

[S Davis]

I have created a macro to add a number of sheets.
But every time when I want to add the 14th sheet, I receive a 1004 error.
The macro is very simple:
for i = 1 to 20
       Sheets("Default").Copy before:=Sheets(2)
next i

In order to do so, I copy a template sheet.

What is wrong?
It looks like a stack overflow?
Do I have to reset some parameters?

I cannot even add a sheet manually after this error.
When I save and re-open I can again add sheets upto 14.
So it is not the total number of sheets that gives me the problem.

Any help?

Hmm. Works fine for me in 2003. What version of excel are you running?

 

[rob]

S. I'm running 2003

Hmm. Works fine for me in 2003. What version of excel are you running?

 

[Tony Zappal]

Hi,
It may be you are running into the cell format issue.
If your copying a template sheet which has alot of formatting on it, you may
be hitting the format limit.
You could try removing alot of the formatting from the template and test
again and see if you get more than 14 sheets.

Cheers,
Tony.

 

[rob]

Indeed, if a remove other sheets etc and data, I can run unto 79 sheets.
I need the formatting.

is there a work around different from .save and .open ( resets somewhere
the memory, but I cannot save the document at that moment)