Copy Method of Sheets Class Failed - after many copies
 

Hi,
I have an application which allows a user to make several selections
and then, based on those, creates an output workbook. The data for this
is taken from one of 5 input files.
To do this I copy input data sheets into my main workbook, manipulate
it, and then save the output file.
I had problems with the number of copies but, after finding some
excellent advice in this group, I now copy the sheets in in groups.
The problem is that having run the first set of selections ok, when the
next selections are made and the copying of the necessary input
happens, I get the error "Run time error 1004, Copy method of Sheets
Class Failed".
If I close the application betweeen runs then it's fine.

I've tried using a second excel application to open the main one and
run the code but the same thing happens.
Is it possible to force a second copy of excel to run so that when it
is closed it will clear out all its memory? If so is that likely to
help?
Any other suggestions would be appreciated.

The only other option I can think of is to force excel to close each
time but then the user would have to open it again to gernerate each
report.

Many thanks.

John Pomfret

Copy Method of Sheets Class Failed - after many copies
 

See this KB
http://support.microsoft.com/default...84&Product=xlw


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


"JzP" wrote in message oups.com...
Hi,
I have an application which allows a user to make several selections
and then, based on those, creates an output workbook. The data for this
is taken from one of 5 input files.
To do this I copy input data sheets into my main workbook, manipulate
it, and then save the output file.
I had problems with the number of copies but, after finding some
excellent advice in this group, I now copy the sheets in in groups.
The problem is that having run the first set of selections ok, when the
next selections are made and the copying of the necessary input
happens, I get the error "Run time error 1004, Copy method of Sheets
Class Failed".
If I close the application betweeen runs then it's fine.

I've tried using a second excel application to open the main one and
run the code but the same thing happens.
Is it possible to force a second copy of excel to run so that when it
is closed it will clear out all its memory? If so is that likely to
help?
Any other suggestions would be appreciated.

The only other option I can think of is to force excel to close each
time but then the user would have to open it again to gernerate each
report.

Many thanks.

John Pomfret

Copy Method of Sheets Class Failed - after many copies
 

Hi Ron,
Many thanks for such a rapid response.

I've looked at the article but still have another question.

I'm sorry if I'm being a bit dim here but what I'm doing at the moment
is running BookA and copying sheets from one of the 5 other workbooks
into BookA. Then all the code in BookA runs and manipulates the data
and finally copies the correct output sheets into, say, BookOut.
BookA thus starts with about 6 sheets in it, has perhaps 50 sheets of
data copied into it, creates another 50 and writes out the created
sheets to the output file. Then the code in BookA deletes all the data
and created sheets.
The user then makes a new selection on the top sheet in BookA and the
whole process starts again.
Because I'm copying into BookA I can't close BookA periodically from
itself, or should I be doing that from the second excel workbook which
I've tried in order to run BookA?
I hope that hasn't just confused matter more!
Thanks again.

John

Copy Method of Sheets Class Failed - after many copies
 

Hi John

I have no time on this moment but will look at it this evening



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


"JzP" wrote in message oups.com...
Hi Ron,
Many thanks for such a rapid response.

I've looked at the article but still have another question.

I'm sorry if I'm being a bit dim here but what I'm doing at the moment
is running BookA and copying sheets from one of the 5 other workbooks
into BookA. Then all the code in BookA runs and manipulates the data
and finally copies the correct output sheets into, say, BookOut.
BookA thus starts with about 6 sheets in it, has perhaps 50 sheets of
data copied into it, creates another 50 and writes out the created
sheets to the output file. Then the code in BookA deletes all the data
and created sheets.
The user then makes a new selection on the top sheet in BookA and the
whole process starts again.
Because I'm copying into BookA I can't close BookA periodically from
itself, or should I be doing that from the second excel workbook which
I've tried in order to run BookA?
I hope that hasn't just confused matter more!
Thanks again.

John

Copy Method of Sheets Class Failed - after many copies
 

Try a periodic save. It may resolve your problem without closing.

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"JzP" wrote in message
oups.com...
Hi Ron,
Many thanks for such a rapid response.

I've looked at the article but still have another question.

I'm sorry if I'm being a bit dim here but what I'm doing at the moment
is running BookA and copying sheets from one of the 5 other workbooks
into BookA. Then all the code in BookA runs and manipulates the data
and finally copies the correct output sheets into, say, BookOut.
BookA thus starts with about 6 sheets in it, has perhaps 50 sheets of
data copied into it, creates another 50 and writes out the created
sheets to the output file. Then the code in BookA deletes all the data
and created sheets.
The user then makes a new selection on the top sheet in BookA and the
whole process starts again.
Because I'm copying into BookA I can't close BookA periodically from
itself, or should I be doing that from the second excel workbook which
I've tried in order to run BookA?
I hope that hasn't just confused matter more!
Thanks again.

John

Copy Method of Sheets Class Failed - after many copies
 

Damon,

Thanks. I tried that but it didn't help. Unfortunately the problem has
got worse as I tried a different input data worksheet and that requires
more sheets/charts to be created so the main application doesn't even
manage to do a single report for that type!
That's what comes of building something when you don't quite know the
scale of data you're going to have to work with...

John

Copy Method of Sheets Class Failed - after many copies
 

Maybe before you run the second report you can close BookA and open it
after a few seconds

Sub closeopen()
Application.OnTime Now + TimeValue("00:00:05"), "yoursub"
ActiveWorkbook.Close False ' not save it
End Sub

Sub yoursub()
Workbooks.Open "C:\BookA.xls"
End Sub

I not test it but try it



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


"JzP" wrote in message ups.com...
Hi again Ron.
Having done further work I may be able to cut down on the copies within
my workbook by using a template - as suggested at the end of the MSKB
article you kindly directed me to.
I'm still concerned that running several reports one after another
without closing excel will cause a problem so if you have any ideas I'd
appreciate it.
Thanks very much for taking the time to assist me.

John Pomfret