vba 复制工作表并获取结果工作表对象？

Copy sheet and get resulting sheet object?

提问by Rabarberski

Is there any easy/shortway to get the worksheet object of the newsheet you get when you copy a worksheet?

是否有任何简单/快捷的方法来获取复制工作表时获得的新工作表的工作表对象？

ActiveWorkbook.Sheets("Sheet1").Copy after:=someSheet

It turns out that the .Copy method returns a Boolean instead of a worksheet object. Otherwise, I could have done:

事实证明 .Copy 方法返回一个布尔值而不是一个工作表对象。否则，我可以这样做：

set newSheet = ActiveWorkbook.Sheets("Sheet1").Copy after:=someSheet

So, I wrote some 25 lines of code to get the object. List all sheets before the copy, list all sheets after, and figure out which one is in the second list only.

因此，我编写了大约 25 行代码来获取对象。列出副本前的所有工作表，列出其后的所有工作表，并仅找出第二个列表中的哪一个。

I am looking for a more elegant, shorter solution.

我正在寻找更优雅、更短的解决方案。

采纳答案by Tim Williams

Dim sht 

With ActiveWorkbook
   .Sheets("Sheet1").Copy After:= .Sheets("Sheet2")
   Set sht = .Sheets(.Sheets("Sheet2").Index + 1)
End With

回答by Trevor Norman

I believe I have finally nailed this issue - it's been driving me nuts, also! It really would have been nice if MS made Copy return a sheet object, same as the Add method...

我相信我终于解决了这个问题——它也让我发疯了！如果 MS 使 Copy 返回一个工作表对象，那真的会很好，就像 Add 方法一样......

The thing is, the index which VBA allocates a newly copied sheet is actually not determined... as others have noted, it very much depends on hidden sheets. In fact, I think the expression Sheets(n) is actually interpreted as "the nth visible sheet". So unless you write a loop testing every sheet's visible property, using this in code is fraught with danger, unless the workbook is protected so users cannot mess with sheets visible property. Too hard...

问题是，VBA 分配新复制的工作表的索引实际上并未确定......正如其他人所指出的，它在很大程度上取决于隐藏的工作表。事实上，我认为表达式 Sheets(n) 实际上被解释为“第 n 个可见表”。因此，除非您编写一个循环测试每个工作表的可见属性，否则在代码中使用它是充满危险的，除非工作簿受到保护，因此用户不能弄乱工作表的可见属性。太难...

My solution to this dilemma is:

我对这个困境的解决方案是：

1. Make the LAST sheet visible (even if temporary)
2. Copy AFTER that sheet. It MUST have index Sheets.Count
3. Hide the former last sheet again, if required - it will now have index Sheets.Count-1
4. Move the new sheet to where you really want it.

1. 使最后一张纸可见（即使是临时的）
2. 在那张纸之后复印。它必须有索引 Sheets.Count
3. 如果需要，再次隐藏前最后一张纸 - 它现在将具有索引 Sheets.Count-1
4. 将新工作表移到您真正需要的位置。

Here's my code - which now seems to be bullet-proof...

这是我的代码 - 现在似乎是防弹的......

Dim sh as worksheet
Dim last_is_visible as boolean

With ActiveWorkbook
    last_is_visible = .Sheets(.Sheets.Count).Visible
    .Sheets(Sheets.Count).Visible = True
    .Sheets("Template").Copy After:=.Sheets(Sheets.Count)
    Set sh=.Sheets(Sheets.Count)
    if not last_is_visible then .Sheets(Sheets.Count-1).Visible = False 
    sh.Move After:=.Sheets("OtherSheet")
End With

In my case, I had something like this (H indicating a hidden sheet)

就我而言，我有这样的东西（H 表示隐藏的工作表）

1... 2... 3(H)... 4(H)... 5(H)... 6... 7... 8(H)... 9(H)

1... 2... 3(H)... 4(H)... 5(H)... 6... 7... 8(H)... 9(H)

.Copy After:=.Sheets(2) actually creates a new sheet BEFORE the next VISIBLE sheet - ie, it became the new index 6. NOT at index 3, as you might expect.

.Copy After:=.Sheets(2) 实际上在下一个 VISIBLE 工作表之前创建了一个新工作表 - 即，它变成了新的索引 6。而不是在索引 3 处，正如您所期望的那样。

Hope that helps ;-)

希望有帮助;-)

回答by Joubarc

Another solution I used would be to copy the sheet to a place where you know its index, aka first. There you can easily have a reference to it for whatever you need, and after that you can move it freely to where you want.

我使用的另一个解决方案是将工作表复制到您知道其索引的地方，也就是首先。在那里，您可以轻松地根据需要引用它，然后您可以将其自由移动到您想要的位置。

Something like this:

像这样的东西：

Worksheets("Sheet1").Copy before:=Worksheets(1)
set newSheet = Worksheets(1)
newSheet.move After:=someSheet

回答by PaulStock

UPDATE:

更新：

Dim ThisSheet As Worksheet
Dim NewSheet As Worksheet
Set ThisSheet = ActiveWorkbook.Sheets("Sheet1")
ThisSheet.Copy
Set NewSheet = Application.ActiveSheet

回答by Mark Moore

I realise this post is over a year old, but I came here looking for an answer to the same issue regarding copying sheets and unexpected results caused by hidden sheets. None of the above really suited what I wanted mainly because of the structure of my workbook. Essentailly it has a very large number of sheets and what is displayed is driven by a user selecting the specific functionality, plus the order of the visible sheets was importnat to me so i didnt want to mess with those. So my end solution was to rely on Excels default naming convention for copied sheets, and explictly rename the new sheet by name. Code sample below (as an aside, my workbook has 42 sheets and only 7 are permanently visible, and the after:=Sheets(Sheets.count)put my copied sheet in the middle of the 42 sheets, depending on what sheets are visible at the time.

我意识到这篇文章已经有一年多了，但我来到这里是为了寻找有关复制工作表和隐藏工作表引起的意外结果的相同问题的答案。以上没有一个真正适合我想要的，主要是因为我的工作簿的结构。基本上它有大量的工作表，显示的内容是由用户选择特定功能驱动的，加上可见工作表的顺序对我来说很重要，所以我不想弄乱这些。所以我的最终解决方案是依赖 Excel 对复制的工作表的默认命名约定，并按名称显式重命名新工作表。下面的代码示例（顺便说一句，我的工作簿有 42 张，只有 7 张是永久可见的， after:=Sheets(Sheets.count)将我复制的工作表放在 42 张工作表的中间，具体取决于当时哪些工作表可见。

        Select Case DCSType
        Case "Radiology"
            'Copy the appropriate Template to a new sheet at the end
            TemplateRAD.Copy after:=Sheets(Sheets.count)
            wsToCopyName = TemplateRAD.Name & " (2)"
            'rename it as "Template"
            Sheets(wsToCopyName).Name = "Template"
            'Copy the appropriate val_Request to a new sheet at the end
            valRequestRad.Copy after:=Sheets(Sheets.count)
            'rename it as "val_Request"
            wsToCopyName = valRequestRad.Name & " (2)"
            Sheets(wsToCopyName).Name = "val_Request"
        Case "Pathology"
            'Copy the appropriate Template to a new sheet at the end
            TemplatePath.Copy after:=Sheets(Sheets.count)
            wsToCopyName = TemplatePath.Name & " (2)"
            'rename it as "Template"
            Sheets(wsToCopyName).Name = "Template"
            'Copy the appropriate val_Request to a new sheet at the end
            valRequestPath.Copy after:=Sheets(Sheets.count)
            wsToCopyName = valRequestPath.Name & " (2)"
            'rename it as "val_Request"
            Sheets(wsToCopyName).Name = "val_Request"
    End Select

Anyway, posted just in case its useful to anyone else

无论如何，发布以防对其他人有用

回答by Rachel Hettinger

Updated with suggestions from Daniel Labelle:

更新了 Daniel Labelle 的建议：

To handle possible hidden sheets, make the source sheet visible, copy it, use the ActiveSheetmethod to return the reference to the new sheet, and reset the visibility settings:

要处理可能隐藏的工作表，使源工作表可见，复制它，使用该ActiveSheet方法返回对新工作表的引用，并重置可见性设置：

Dim newSheet As Worksheet
With ActiveWorkbook.Worksheets("Sheet1")
    .Visible = xlSheetVisible
    .Copy after:=someSheet
    Set newSheet = ActiveSheet
    .Visible = xlSheetHidden ' or xlSheetVeryHidden
End With

回答by alrm3000

This should be a comment in response to @TimWilliams, but it's my first post so I can't comment.

这应该是对@TimWilliams 的回应，但这是我的第一篇文章，所以我无法发表评论。

This is an example of the problem @RBarryYoung mentioned, related to hidden sheets. There is a problem when you try to put your copy after the last sheet and the last sheet is hidden. It seems that, if the last sheet is hidden, it always retains the highest index, so you need something like

这是@RBarryYoung 提到的与隐藏表相关的问题的一个示例。当您尝试将副本放在最后一张纸之后并且最后一张纸被隐藏时会出现问题。似乎，如果最后一张表被隐藏，它总是保留最高的索引，所以你需要像

Dim sht As Worksheet

With ActiveWorkbook
   .Sheets("Sheet1").Copy After:=.Sheets(.Sheets.Count)
   Set sht = .Sheets(.Sheets.Count - 1)
End With

Similar situation when you try to copy before a hidden first sheet.

当您尝试在隐藏的第一张纸之前复制时，情况类似。

回答by Tigregalis

Based on Trevor Norman's method, I've developed a function for copying a sheet and returning a reference to the new sheet.

基于Trevor Norman 的方法，我开发了一个用于复制工作表并返回对新工作表的引用的函数。

1. Unhide the last sheet (1) if not visible
2. Copy the source sheet (2) after the last sheet (1)
3. Set the reference to the new sheet (3), i.e. the sheet after the last sheet (1)
4. Hide the last sheet (1) if necessary

1. 如果不可见，则取消隐藏最后一张纸 (1)
2. 在最后一个工作表 (1) 之后复制源工作表 (2)
3. 设置对新工作表 (3) 的引用，即最后一张工作表 (1) 之后的工作表
4. 如有必要，隐藏最后一张纸 (1)

Code:

代码：

Function CopySheet(ByRef sourceSheet As Worksheet, Optional ByRef destinationWorkbook As Workbook) As Worksheet

    Dim newSheet As Worksheet
    Dim lastSheet As Worksheet
    Dim lastIsVisible As XlSheetVisibility

    If destinationWorkbook Is Nothing Then Set destinationWorkbook = sourceSheet.Parent

    With destinationWorkbook
        Set lastSheet = .Worksheets(.Worksheets.Count)
    End With

    ' store visibility of last sheet
    lastIsVisible = lastSheet.Visible
    ' make the last sheet visible
    lastSheet.Visible = xlSheetVisible

    sourceSheet.Copy After:=lastSheet
    Set newSheet = lastSheet.Next

    ' restore visibility of last sheet
    lastSheet.Visible = lastIsVisible

    Set CopySheet = newSheet

End Function

This will always insert the copied sheet at the end of the destination workbook.

这将始终在目标工作簿的末尾插入复制的工作表。

After this, you can do any moves, renames, etc.

在此之后，您可以进行任何移动、重命名等。

Usage:

用法：

Sub Sample()

    Dim newSheet As Worksheet

    Set newSheet = CopySheet(ThisWorkbook.Worksheets("Template"))

    Debug.Print newSheet.Name

    newSheet.Name = "Sample" ' rename new sheet
    newSheet.Move Before:=ThisWorkbook.Worksheets(1) ' move to beginning

    Debug.Print newSheet.Name

End Sub

Or if you want the behaviour/interface to be more similar to the built-in Copy method (i.e. before/after), you could use:

或者，如果您希望行为/界面更类似于内置的 Copy 方法（即之前/之后），您可以使用：

Function CopySheetTo(ByRef sourceSheet As Worksheet, Optional ByRef beforeSheet As Worksheet, Optional ByRef afterSheet As Worksheet) As Worksheet

    Dim destinationWorkbook As Workbook
    Dim newSheet As Worksheet
    Dim lastSheet As Worksheet
    Dim lastIsVisible As XlSheetVisibility

    If Not beforeSheet Is Nothing Then
        Set destinationWorkbook = beforeSheet.Parent
    ElseIf Not afterSheet Is Nothing Then
        Set destinationWorkbook = afterSheet.Parent
    Else
        Set destinationWorkbook = sourceSheet.Parent
    End If

    With destinationWorkbook
        Set lastSheet = .Worksheets(.Worksheets.Count)
    End With

    ' store visibility of last sheet
    lastIsVisible = lastSheet.Visible
    ' make the last sheet visible
    lastSheet.Visible = xlSheetVisible

    sourceSheet.Copy After:=lastSheet
    Set newSheet = lastSheet.Next

    ' restore visibility of last sheet
    lastSheet.Visible = lastIsVisible

    If Not beforeSheet Is Nothing Then
        newSheet.Move Before:=beforeSheet
    ElseIf Not afterSheet Is Nothing Then
        newSheet.Move After:=afterSheet
    Else
        newSheet.Move After:=sourceSheet
    End If

    Set CopySheetTo = newSheet

End Function

回答by Ama

As already mentioned here, copy/paste the sheet to the very left (index = 1), then assign it to a variable, then move it where you would like.

正如这里已经提到的，将工作表复制/粘贴到最左边（索引 = 1），然后将其分配给一个变量，然后将其移动到您想要的位置。

Function CopyWorksheet(SourceWorksheet As Worksheet, AfterDestinationWorksheet As Worksheet) As Worksheet

    Dim DestinationWorkbook As Workbook
    Set DestinationWorkbook = AfterDestinationWorksheet.Parent

    Dim FirstSheetVisibility As XlSheetVisibility
    FirstSheetVisibility = DestinationWorkbook.Sheets(1).Visible

    DestinationWorkbook.Sheets(1).Visible = xlSheetVisible
    SourceWorksheet.Copy Before:=DestinationWorkbook.Sheets(1)
    DestinationWorkbook.Sheets(2).Visible = FirstSheetVisibility

    Dim NewWorksheet As Worksheet
    Set NewWorksheet = DestinationWorkbook.Sheets(1)

    NewWorksheet.Move After:=AfterDestinationWorksheet

    Set CopyWorksheet = NewWorksheet

End Function

回答by Daniel Labelle

It is correct that hidden worksheets cause the new worksheet index to be non-sequential on either side of the source worksheet. I found that Rachel's answer works if you're copying before. But you'd have to adjust it if you're copying after.

隐藏的工作表导致新工作表索引在源工作表的任一侧都是非连续的，这是正确的。我发现如果您之前复制，雷切尔的答案有效。但是如果您要复制，则必须对其进行调整。

Once the model is visible and copied, the new worksheet object is simply the ActiveSheet whether you copy the source before or after.

一旦模型可见并被复制，新的工作表对象就是 ActiveSheet，无论您是在之前还是之后复制源。

As a preference, you could replace:

作为首选，您可以替换：

Set newSheet = .Previouswith Set newSheet = Application.ActiveSheet.

Set newSheet = .Previous与设置newSheet = Application.ActiveSheet。

Hope this is helpful to some of you.

希望这对你们中的一些人有帮助。