VBA 将工作表复制到工作簿末尾（带有隐藏的工作表）

VBA Copy Sheet to End of Workbook (with Hidden Worksheets)

提问by enderland

I want to copy a sheet and add it to the end of all current sheets (regardless of whether the sheets are hidden).

我想复制一张工作表并将其添加到所有当前工作表的末尾（无论工作表是否隐藏）。

Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).name = "copied sheet!"

This works fine, except, when there are hidden sheets, the new sheet is only inserted after the last visible worksheet, so the namecommand renames the wrong sheet.

这工作正常，除了当有隐藏工作表时，新工作表仅插入在最后一个可见工作表之后，因此该name命令重命名了错误的工作表。

I have tried variations of the following to get a reference to the newly copied WorkSheetbut none were successful and/or valid code.

我尝试了以下变体来获取对新复制的引用，WorkSheet但没有成功和/或有效代码。

Dim test As Worksheet
Set test = Sheets(1).Copy(After:=Sheets(Sheets.Count))
test.Name = "copied sheet!"

回答by Siddharth Rout

Try this

尝试这个

Sub Sample()
    Dim test As Worksheet
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    Set test = ActiveSheet
    test.Name = "copied sheet!"
End Sub

回答by sidnc86

Make the source sheet visible before copying. Then copy the sheet so that the copy also stays visible. The copy will then be the active sheet. If you want, hide the source sheet again.

在复制之前使源工作表可见。然后复制工作表，使副本也保持可见。副本将成为活动工作表。如果需要，请再次隐藏源工作表。

回答by Jabaal

If you use the following code based on @Siddharth Rout's code, you rename the just copied sheet, no matter, if it is activated or not.

如果您根据@Siddharth Rout 的代码使用以下代码，则无论是否已激活，您都会重命名刚刚复制的工作表。

Sub Sample()

    ThisWorkbook.Sheets(1).Copy After:=Sheets(Sheets.Count)
    ThisWorkbook.Sheets(Sheets.Count).Name = "copied sheet!"

End Sub

回答by dra_red

I faced a similar issue while copying a sheet to another workbook. I prefer to avoid using 'activesheet' though as it has caused me issues in the past. Hence I wrote a function to perform this inline with my needs. I add it here for those who arrive via google as I did:

我在将工作表复制到另一个工作簿时遇到了类似的问题。我更喜欢避免使用“activesheet”，因为它过去曾给我带来过问题。因此，我编写了一个函数来根据我的需要执行此操作。我在这里为那些像我一样通过谷歌到达的人添加它：

The main issue here is that copying a visible sheet to the last index position results in Excel repositioning the sheet to the end of the visible sheets. Hence copying the sheet to the position after the last visible sheet sorts this issue. Even if you are copying hidden sheets.

这里的主要问题是将可见工作表复制到最后一个索引位置会导致 Excel 将工作表重新定位到可见工作表的末尾。因此，将工作表复制到最后一个可见工作表之后的位置可以解决此问题。即使您正在复制隐藏的工作表。

Function Copy_WS_to_NewWB(WB As Workbook, WS As Worksheet) As Worksheet
    'Creates a copy of the specified worksheet in the specified workbook
    '   Accomodates the fact that there may be hidden sheets in the workbook

    Dim WSInd As Integer: WSInd = 1
    Dim CWS As Worksheet

    'Determine the index of the last visible worksheet
    For Each CWS In WB.Worksheets
        If CWS.Visible Then If CWS.Index > WSInd Then WSInd = CWS.Index
    Next CWS

    WS.Copy after:=WB.Worksheets(WSInd)
    Set Copy_WS_to_NewWB = WB.Worksheets(WSInd + 1)

End Function

To use this function for the original question (ie in the same workbook) could be done with something like...

要将此功能用于原始问题（即在同一工作簿中），可以使用类似...

Set test = Copy_WS_to_NewWB(Workbooks(1), Workbooks(1).Worksheets(1))
test.name = "test sheet name"

回答by TADbit

Add this code to the beginning:

将此代码添加到开头：

    Application.ScreenUpdating = False
     With ThisWorkbook
      Dim ws As Worksheet
       For Each ws In Worksheets: ws.Visible = True: Next ws
     End With

Add this code to the end:

将此代码添加到最后：

    With ThisWorkbook
     Dim ws As Worksheet
      For Each ws In Worksheets: ws.Visible = False: Next ws
    End With
     Application.ScreenUpdating = True

Adjust Code at the end if you want more than the first sheet to be active and visible. Such as the following:

如果您希望第一个工作表以外的其他工作表处于活动状态和可见状态，请在最后调整代码。例如以下内容：

     Dim ws As Worksheet
      For Each ws In Worksheets
       If ws.Name = "_DataRecords" Then

         Else: ws.Visible = False
       End If
      Next ws

To ensure the new sheet is the one renamed, adjust your code similar to the following:

为确保新工作表是重命名的工作表，请调整您的代码，类似于以下内容：

     Sheets(Me.cmbxSheetCopy.value).Copy After:=Sheets(Sheets.Count)
     Sheets(Me.cmbxSheetCopy.value & " (2)").Select
     Sheets(Me.cmbxSheetCopy.value & " (2)").Name = txtbxNewSheetName.value

This code is from my user form that allows me to copy a particular sheet (chosen from a dropdown box) with the formatting and formula's that I want to a new sheet and then rename new sheet with the user Input. Note that every time a sheet is copied it is automatically given the old sheet name with the designation of " (2)". Example "OldSheet" becomes "OldSheet (2)" after the copy and before the renaming. So you must select the Copied sheet with the programs naming before renaming.

此代码来自我的用户表单，它允许我将具有格式和公式的特定工作表（从下拉框中选择）复制到新工作表中，然后使用用户输入重命名新工作表。请注意，每次复印工作表时，都会自动赋予旧工作表名称并指定“(2)”。示例“OldSheet”在复制之后和重命名之前变为“OldSheet (2)”。因此，您必须在重命名之前选择带有程序命名的复制表。

回答by fsacred

When you want to copy a sheet named "mySheet" and use .Copy After:=, Excel first names the copied sheet exactly the same and simply adds ' (2)' so that its final name is "mySheet (2)".

当您要复制名为“mySheet”的工作表并使用 .Copy After:= 时，Excel 首先将复制的工作表命名为完全相同，并简单地添加“(2)”，使其最终名称为“mySheet (2)”。

Hidden or Not, doesn't matter. It rocks with 2 lines of code, adding the copied sheet at the end of the Workbook!!!

隐藏与否，无所谓。它包含 2 行代码，在工作簿末尾添加复制的工作表！！！

Example:

例子：

Sheets("mySheet").Copy After:=Sheets(ThisWorkbook.Sheets.count)
Sheets("mySheet (2)").name = "TheNameYouWant"

Simple no!

简单不！

回答by Nabil Amer

Answer :I found this and wants to share it with you.

答：我发现了这个并想与您分享。

Sub Copier4()
   Dim x As Integer

   For x = 1 To ActiveWorkbook.Sheets.Count
      'Loop through each of the sheets in the workbook
      'by using x as the sheet index number.
      ActiveWorkbook.Sheets(x).Copy _
         After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
         'Puts all copies after the last existing sheet.
   Next
End Sub

But the question, can we use it with following code to rename the sheets, if yes, how can we do so?

但问题是，我们可以用下面的代码来重命名工作表吗，如果是，我们该怎么做？

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Summary").Range("A10")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub