Sheets.copy problem

[Guest]

I have the following line of code in a model I have been developing.

Sheets("Standard data sheet").Copy Before:=Sheets("End data sheet")

Essentially my procedure involves copying the standard data sheet to
specific section of the workbook by clicking on a button. The standard sheet
is renamed with the contents of a range on the worksheet and users are
returned to the standard data sheet to enter another set of data. The intent
is that the users should be able to repeat this as often as necessary.

The procedure works fine for about 8 - 10 copies of the standard sheet, but
then on the next copy comes up with a 'run time 1004' error. The procedure
will work fine again, for a further 8 - 10 copies, if the model is closed and
re-opened again.

I have read that there was a bug in earlier versions of Excel relating to
this, but it was suggested that it was fixed in more recent versions. This
does not appear to be the case. I am using Excel 2002 SP3.

Any clarifications or suggested alternative work arounds (ie other than
closing and re-opening the file) would be appreciated.

 

[Guest]

Ron:

It's not an Excel problem so much as it is an Excel limitation. The
number of worksheets allowed in a workbook is dependent on the total volumn
of data in the workbook AND the amount of memory on your computer. If you
have one or two worksheets crammed full of data, you'll get a lower number of
allowable worksheets. Or, you can have a lot of sparsely populated
worksheets. In any case, keep in mind that as Excel is manipulating your
data, lots of extra memory is being used to keep track of what's going where.
That's why you die after 8-10 sheets (you basically run out of memory).
BUT, when you close the workbook, Excel cleans up all of the junk which is
why you can come back in and get another 8-10 sheets before the whole thing
mucks up, again. Unfortunately, doing a SAVE somewhere in the middle of all
this doesn't fix the problem - you have to close and re-open the workbook.
Programmatically, I would open a second workbook using VBA code and
activate it. Then, I would close the original workbook and re-open it,
re-activate it, and return control to the user. That way, Excel (itself) is
not actually shut-down but the heartburn workbook is 'reset'.

Steve in Ohio