Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why am I getting R/T 1004 - Copy method of Rng class failed? | Excel Discussion (Misc queries) | |||
Copy Method of WorkSheet Class Failed! | Excel Programming | |||
Copy method of chart class failed | Excel Programming | |||
RE :Copy method of Worksheet class failed? | Excel Programming | |||
Copy method of Worksheet class failed? | Excel Programming |