Why Worksheet.Copy does not return a link to a newly created workbook

Question

I have code where wb is an existing workbook with multiple worksheets. If I copy one of the "Overview" sheets, a new workbook is created - why the following error occurs: "Object is required" ?:

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

Answer 1

The Worksheet.Copy method does not return a link to a new workbook. Instead, you can use the Worksheet link:

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

As you know, if you did not provide an After or Before argument, it copies the new workbook. Copying within the same book will use this code:

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

If you want to copy a sheet into a new book and save a link to it, you need to do this 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 the link to the new book, just omit the last line (and the variable declaration for wsCopied).

Answer 2

This is one of the few times you should use one of the Active* objects Active*

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

Answer 3

The copy worksheet method seems to return a boolean value, not a workbook object. To link to a book, 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 

Answer 4

Today I came across the same thing. For me, active * objects are too vague, so I was looking for a way to reliably identify the sheet object. Just if someone is looking for this, here is for the files:

 dim sws, tws as worksheet set sws = thisworkbook.sheets("source") sws.copy before:=sws set tws = sws.previous 

It does the job

rn43x