Copy Method of Worksheet Class Failed

[Steph]

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!

 

[Ron de Bruin]

Try the examples on this page and post back
if you got problems.

http://www.rondebruin.nl/copy3.htm