Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Method of Worksheet Class Failed
Half of the files are 96kb, half are 5.4Mb. I did use the commented
out are of your code to eliminate the formulas. I also had that bid of code in my original procedure. I'm baffled! Strange How big are the files in Size? Is this also happen if you convert the sheets to values (Use also the commented code ) -- Regards Ron de Bruin http://www.rondebruin.nl "Steph" wrote in message om... Ron, I ran your code (Copy a sheet from each workbook into your workbook) and still get the same error! It errors out on roughly the 34th sheet. "Ron de Bruin" wrote in message ... Try the examples on this page and post back if you got problems. http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Steph" wrote in message om... Good afternoon everyone. I have a procedure that opens 60 files and copied 1 sheet from each file into a single workbook to consolidate the data. The procedure runs great until I get the the 35th file, and then I get an error: Copy method of Worksheet class failed. The copy code I was using is below: wkbk.Sheets("Opex & CAPEX").CopyAfter:=ThisWorkbook.Worksheets(3) With ActiveSheet.UsedRange .Value = .Value End With Jim Rech gave me a link to a Microsoft Knowledge base article, stating that Microsoft has confirmed this to be an Excel 97 problem, which was corrected in later versions (PS - I'm using Excel 2003, and it's still a problem!). They also gave a workaround, which I incorporated: wkbk.Sheets("Opex & CAPEX").Cells.Copy ThisWorkbook.Sheets.Add ThisWorkbook.ActiveSheet.Paste With ThisWorkbook.ActiveSheet.UsedRange .Value = .Value End With This code seems to work, but is painfully slow. Can anyone recommend a quicker way to accomplish this? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Method of Worksheet Class Failed
Hi
If you start with a new workbook and copy the macro in it do you get the same result then. -- Regards Ron de Bruin http://www.rondebruin.nl "Steph" wrote in message m... Half of the files are 96kb, half are 5.4Mb. I did use the commented out are of your code to eliminate the formulas. I also had that bid of code in my original procedure. I'm baffled! Strange How big are the files in Size? Is this also happen if you convert the sheets to values (Use also the commented code ) -- Regards Ron de Bruin http://www.rondebruin.nl "Steph" wrote in message om... Ron, I ran your code (Copy a sheet from each workbook into your workbook) and still get the same error! It errors out on roughly the 34th sheet. "Ron de Bruin" wrote in message ... Try the examples on this page and post back if you got problems. http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Steph" wrote in message om... Good afternoon everyone. I have a procedure that opens 60 files and copied 1 sheet from each file into a single workbook to consolidate the data. The procedure runs great until I get the the 35th file, and then I get an error: Copy method of Worksheet class failed. The copy code I was using is below: wkbk.Sheets("Opex & CAPEX").CopyAfter:=ThisWorkbook.Worksheets(3) With ActiveSheet.UsedRange .Value = .Value End With Jim Rech gave me a link to a Microsoft Knowledge base article, stating that Microsoft has confirmed this to be an Excel 97 problem, which was corrected in later versions (PS - I'm using Excel 2003, and it's still a problem!). They also gave a workaround, which I incorporated: wkbk.Sheets("Opex & CAPEX").Cells.Copy ThisWorkbook.Sheets.Add ThisWorkbook.ActiveSheet.Paste With ThisWorkbook.ActiveSheet.UsedRange .Value = .Value End With This code seems to work, but is painfully slow. Can anyone recommend a quicker way to accomplish this? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 1004 Copy method of worksheet class failed | Excel Discussion (Misc queries) | |||
Copy Method of Worksheet Class Failed | Excel Programming | |||
Copy Method of Worksheet Class Failed | Excel Programming | |||
RE :Copy method of Worksheet class failed? | Excel Programming | |||
Copy method of Worksheet class failed? | Excel Programming |