Copy Method of Worksheet Class Failed
 

Yes, I copied the code into a new workbook, and it errored out at the
same exact place.


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!

Copy Method of Worksheet Class Failed
 

Hi Steph

If you send me one of the workbooks I will test it for you also in 2003.
Is it always the same workbook where the macro stop?

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


"Steph" wrote in message om...
Yes, I copied the code into a new workbook, and it errored out at the
same exact place.


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!