I have written some code in VBA to loop X number of times and create a
new worksheet during each iteration with some data in from a master
worksheet. When I first start-up Excel, I can create over 40 worksheets
without a problem, however if clear all the worksheets (delete them) and
run the code again (to create another 40), Excel/VBA produces this error
message:

Run-time error '1004':

Method 'Copy' of object '_Worksheet' failed

Is this Excel running out of memory? If so, how can I clear it during
each iteration or at the end of the cycle?

Thanks!


--
kdev
------------------------------------------------------------------------
kdev's Profile: http://www.excelforum.com/member.php...o&userid=16530
View this thread: http://www.excelforum.com/showthread...hreadid=319272

Try a line to Save the workbook after deleting the sheets and before adding
new ones. Excel keeps track of all deleted sheets until saved or closed so
you are probably loading up memory and the Undo buffer.

Mike F
"kdev" wrote in message
...

I have written some code in VBA to loop X number of times and create a
new worksheet during each iteration with some data in from a master
worksheet. When I first start-up Excel, I can create over 40 worksheets
without a problem, however if clear all the worksheets (delete them) and
run the code again (to create another 40), Excel/VBA produces this error
message:

Run-time error '1004':

Method 'Copy' of object '_Worksheet' failed

Is this Excel running out of memory? If so, how can I clear it during
each iteration or at the end of the cycle?

Thanks!


--
kdev
------------------------------------------------------------------------
kdev's Profile:
http://www.excelforum.com/member.php...o&userid=16530
View this thread: http://www.excelforum.com/showthread...hreadid=319272

Tried putting in a line to save the workbook during each iteration but
the error message still appears after creating the second batch of
labels. Excel may keep a record of deleted sheets, etc but not when
running macros. For example, my macro which loops through the workbooks
deletes X sheets, 1 per iteration. Excel cannot undo this.

This error message seems to be a Microsoft generic message which can be
applied to most problems. No-one seems to have a working solution. There
are numerous postings around the web by people like myself, almost none
of which can find a solution/workaround. I have read rubbish like 'this
is a MS Excel 97 problem which is fixed for 2000'. Well, I have version
2002.

So to all those who stumble upon this thread looking for a solution,
you may as well stop now and consign your efforts to the past.

If anyone does fingure out a solution I will gladly edit my ramblings.

Good luck to those trying to acheive equally fruitless endeavors in
Microsoft's 90% finished applications (where the missing 10% is made up
of updates to be issued over the coming years).

KDev is signing off...


--
kdev
------------------------------------------------------------------------
kdev's Profile: http://www.excelforum.com/member.php...o&userid=16530
View this thread: http://www.excelforum.com/showthread...hreadid=319272

Hi kdev

Read this KB

Copying Worksheet Programmatically Causes Run-Time Error 1004
http://support.microsoft.com/default...84&Product=xlw
--
Regards Ron de Bruin
http://www.rondebruin.nl


"kdev" wrote in message ...

I have written some code in VBA to loop X number of times and create a
new worksheet during each iteration with some data in from a master
worksheet. When I first start-up Excel, I can create over 40 worksheets
without a problem, however if clear all the worksheets (delete them) and
run the code again (to create another 40), Excel/VBA produces this error
message:

Run-time error '1004':

Method 'Copy' of object '_Worksheet' failed

Is this Excel running out of memory? If so, how can I clear it during
each iteration or at the end of the cycle?

Thanks!


--
kdev
------------------------------------------------------------------------
kdev's Profile: http://www.excelforum.com/member.php...o&userid=16530
View this thread: http://www.excelforum.com/showthread...hreadid=319272

Thanks Ron de Bruin!

So Microsoft even admit that there is a problem and considering that
that document Ron pointed out was written October 8 2003 and it is now
November 2004!!

I now have a working solution, it is not the quickest application in
the world, but it works!


--
kdev
------------------------------------------------------------------------
kdev's Profile: http://www.excelforum.com/member.php...o&userid=16530
View this thread: http://www.excelforum.com/showthread...hreadid=319272

but it works!

That's important<g


--
Regards Ron de Bruin
http://www.rondebruin.nl


"kdev" wrote in message ...

Thanks Ron de Bruin!

So Microsoft even admit that there is a problem and considering that
that document Ron pointed out was written October 8 2003 and it is now
November 2004!!

I now have a working solution, it is not the quickest application in
the world, but it works!


--
kdev
------------------------------------------------------------------------
kdev's Profile: http://www.excelforum.com/member.php...o&userid=16530
View this thread: http://www.excelforum.com/showthread...hreadid=319272