Copy Method of Worksheet Class Failed
 

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
 

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!