I created a macro to quickly copy sheets from a series of workbooks
containing 5-15 sheets each to a master "All" workbook. This works fine for
several workbooks, but then, sometimes at the beginning of a workbook copy
and sometimes in the middle, it gives an error €œCopy method of Worksheet
class failed€�, and the €œSheets(cnt).Copy After:=Workbooks(This).Sheets(xx)€�
statement is highlighted in yellow. At this point a total of roughly xx = 45
sheets have been copied from several workbooks.

In order to get this to work again, I need to close the €œAll€� workbook and
re-open it. Then I can continue to use the macro for several more workbook
combines until I get the error again. Why is this happening?

Sub Combine() 'macro starts in a workbook that is being copied from.
Dim x, xx, cnt
Dim This As String
Dim All As String
All = "Data - CSR Monthly Report - Sep2009final.xls" 'destination workbook
x = Sheets.Count
This = ActiveWorkbook.Name
For cnt = 1 To x
Workbooks(This).Activate
xx = Workbooks(All).Sheets.Count
Sheets(cnt).Copy After:=Workbooks(All).Sheets(xx)
Next
Workbooks(This).Activate
End Sub