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).

Answer 2

This is one of the few occasions you have to use one of the Active* objects

wb.Sheets("Overview").Copy
Set wbCopy = ActiveWorkbook

Answer 3

The worksheets copy method appears to return a boolean value rather than a workbook object. To set a reference to the workbook you can use the following.

Sub wbcopy()

Dim wbcopy As Excel.Workbook
Dim wbIndex As Excel.Workbook
Dim sArray() As String
Dim iIndex As Integer
Dim bfound As Boolean
Dim wb As Workbook

Set wb = ThisWorkbook

ReDim sArray(Workbooks.Count)

'Find the names of all the current workbooks
For Each wbIndex In Workbooks
    sArray(iIndex) = wbIndex.FullName
Next wbIndex

'Copy the sheet to a new workbook
wb.Sheets("Overview").Copy

'Find the sheet with the new name
For Each wbIndex In Workbooks

    bfound = False

    For iIndex = LBound(sArray) To UBound(sArray)
        If wbIndex.FullName = sArray(iIndex) Then
            bfound = True
            Exit For
        End If
    Next iIndex

    If Not bfound Then
        Set wbcopy = wbIndex
        Exit For
    End If

Next wbIndex

End Sub