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

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

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

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

Macro to copy sheets to another workbook fails after about 45
 

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

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

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