vba 为什么 Worksheet.Copy 不返回对创建的新工作簿的引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18428762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does Worksheet.Copy not return a reference to the new workbook created
提问by whytheq
I have some code where wbis 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"?:
我有一些代码，其中wb是现有的多工作表工作簿。如果我复制其中一张“概述”工作表，则会创建一个新工作簿 - 那么为什么会出现以下错误提示“需要对象”？：
Dim wbCopy As Excel.Workbook
Set wbCopy = wb.Sheets("Overview").Copy
回答by Andy G
The Worksheet.Copymethod doesn't return a reference to the new Workbook. You might use a Worksheet reference instead:
该Worksheet.Copy方法不返回对新工作簿的引用。您可以改用工作表引用：
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:
如您所知，如果您不提供 After 或 Before 参数，它将复制到新的工作簿。在同一工作簿中复制将使用以下代码：
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).
如果您只需要保留对新工作簿的引用，则只需省略最后一行（以及 wsCopied 的变量声明）。
回答by chris neilsen
This is one of the few occasions you have to use one of the Active*objects
这是您必须使用其中一个Active*对象的少数情况之一
wb.Sheets("Overview").Copy
Set wbCopy = ActiveWorkbook
回答by Graham Anderson
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
回答by rn43x
I ran into the same thing today. To me the active* objects is too vague so I looked for a way to reliably determine the sheet object. Just if anyone's looking for this, here's for the files:
我今天遇到了同样的事情。对我来说，活动 * 对象太模糊了，所以我寻找一种方法来可靠地确定工作表对象。如果有人在寻找这个，这里是文件：
dim sws, tws as worksheet
set sws = thisworkbook.sheets("source")
sws.copy before:=sws
set tws = sws.previous
That does the job
那做的工作
rn43x
43x
