excel - Why does Worksheet.Copy not return a reference to the new workbook created

Question

I have some code where wb is an existing multi-worksheet workbook. If I copy one of the sheets "Overview" a new workbook is created - so why does the following error saying "object required"?:

Dim wbCopy As Excel.Workbook
Set wbCopy = wb.Sheets("Overview").Copy

Answer 1

The Worksheet.Copy method doesn't return a reference to the new Workbook. You might use a Worksheet reference instead:

Dim wsCopy As Excel.Worksheet    'changed from wb to wsCopy

As you know, if you don't supply either the After or Before argument it copies to a new Workbook. Copying within the same workbook would use this code:

Set wsCopy = wb.Worksheets("Overview")
wsCopy.Copy After:= wb.Worksheets(1)    'or Before:=

If you want to copy the sheet to a new workbook, and retain a reference to it, then this needs to be done in stages:

Dim wbNew As Excel.Workbook
Dim wsCopied As Excel.Worksheet

Set wbNew = Workbooks.Add
wsCopy.Copy before:=wbNew.Worksheets(1)
Set wsCopied = wbNew.Worksheets(1)

If you only need to keep a reference to the new workbook then just omit the last line (and the variable declaration for wsCopied).