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

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


"AZSteve" wrote:

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

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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


"AZSteve" wrote:

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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"AZSteve" wrote in message
...
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