Macro to copy sheets to another workbook fails after about 45 copi

[AZSteve]

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

 

[Jacob Skaria]

Use the workbook object...Also you dont need to loop...Try the below and
feedback

Sub Combine()
Dim wbThis As Workbook, wbDest As Workbook
Set wbDest = Workbooks("Data - CSR Monthly Report - Sep2009final.xls")
Set wbThis = ActiveWorkbook
wbThis.Sheets.Copy After:=wbDest.Sheets(wbDest.Sheets.Count)

''OR using a loop
'For Each ws In wbThis
' ws.Copy After:=wbDest.Sheets(wb.Sheets.Count)
'Next
End Sub

If this post helps click Yes

 

[Jacob Skaria]

correction incase you try out the Loop..

For Each ws In wbThis
ws.Copy After:=wbDest.Sheets(wbDest.Sheets.Count)
Next

If this post helps click Yes

 

[Ron de Bruin]

See
http://support.microsoft.com/kb/210684/en-us