I changed my approach and tried the following:
With ThisWorkbook
.Sheets("Sheet1").Copy
Set wbResults = ActiveWorkbook
.Sheets("Sheet2").Copy after:=wbResults.Sheets(wbResults.Sheets.Count)
.Sheets("Sheet3").Copy after:=wbResults.Sheets(wbResults.Sheets.Count)
.Sheets("Sheet4").Copy after:=wbResults.Sheets(wbResults.Sheets.Count)
.Sheets("Sheet5").Copy after:=wbResults.Sheets(wbResults.Sheets.Count)
.Sheets("Sheet6").Copy after:=wbResults.Sheets(wbResults.Sheets.Count)
.Sheets("Sheet7").Copy after:=wbResults.Sheets(wbResults.Sheets.Count)
.Sheets("Sheet8").Copy after:=wbResults.Sheets(wbResults.Sheets.Count)
.Sheets("Sheet9").Copy after:=wbResults.Sheets(wbResults.Sheets.Count)
.Sheets("Sheet10").Copy after:=wbResults.Sheets(wbResults.Sheets.Count)
End With
(wbResults is defined as a Workbook object)
It copies the first three sheets then fails with:
-2147417848
Automation error - The object invoked has disconnected from its clients.
Bookmarks