Inconsistent error with Sheets.Copy

I have a suite of spreadsheets. All material code is in a file called Control.xls, with EnableEvents false and calculation manual. Another file (referred to internally as wbTCR) contains detailed reports. A third (wbRpt) is being assembled from the detailed and summary reports. The summary report comes from a fourth file (wbNew). This contains a SAP BW query, and the file is repeatedly opened, the query refreshed with different parameters, the report copied and the file closed. It is at the point where the report is copied that I have a problem. The line copying the report is:

wbNew.Sheets("Summary").Copy after:=wbRpt.Sheets(wbRpt.Sheets.Count)

The number of sheets in each assembly changes depending on the number of children that each parent has, and this changes from month to month. Last month, the code worked perfectly. This month, I sometimes get an error on this line. The error occurs on the ninth, tenth or eleventh child, i.e. when the number of sheets is 19, 21 or 23. It is possible that some parents with more than 10 children have run without problems. The error is:

Error 1004 - Copy method of Worksheet class failed

I have inserted the following two lines before the problem line, and they work around the problem:

wbNew.Sheets("Summary").Copy
Workbooks(Workbooks.Count).Close

There is no conflict in sheet names. The crash occurs at the same point for a given parent, but I can see no reason why the point should change from one parent to another. Indeed, I can see no reason why the line should stop working, having worked last month and having worked for the first 8 or more calls this time.

Has anyone seen anything like this? If so, can you give me any clues?

Regards,

John Davies

Hi John

It's a common problem - check out the following MS article:

http://support.microsoft.com/kb/210684

Richard