为什么Worksheet.Copy不会返回对创建的新工作簿的引用 [英] Why does Worksheet.Copy not return a reference to the new workbook created

问题描述

我有一些代码，其中 wb 是现有的多工作表工作簿。如果我复制其中一张表概述，则会创建一个新的工作簿 - 为什么会出现以下错误提示对象需要？：

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

推荐答案

Worksheet.Copy 方法不会返回对新工作簿的引用。您可以使用工作表参考：

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

如你所知，如果不提供After或Before参数，它将复制到新的Workbook。在相同的工作簿中复制将使用以下代码：

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)

如果您只需要保留对新工作簿的引用，那么只需省略最后一行（和wsCopied的变量声明）。

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

这篇关于为什么Worksheet.Copy不会返回对创建的新工作簿的引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！