Hi Ron,

I don't know if sending you a workbook will help. If I run the code
on 1 book, or 5 books, or 25 books the code works fine. It always
seems to die around the 35th book. I'm not sure if it's a size/memory
thing or not. I guess I can try to copy a buch of the 90Kb files, and
run the code on only those files to see how many books of 90Kb each
the code will chunk through.

Also, on a side note, the workaround that MS proposed I think will
work, but much slower. Apparently inserting a blank sheet and then
copying to it is ok. But copying (pressing ctl and dragging a sheet
to copy it) doesn't work past a certain point.

I'm also annoyed that MS said this problem was fixed post Excel 97!



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!