vba 将整个工作表复制到 Excel 2010 中的新工作表

Copy an entire worksheet to a new worksheet in Excel 2010

提问by brettdj

I have found similar questions that deal with copying an entire worksheet in one workbook and pasting it to another workbook, but I am interested in simply copying an entire worksheet and pasting it to a new worksheet -- in the same workbook.

我发现了类似的问题，这些问题涉及将整个工作表复制到一个工作簿中并将其粘贴到另一个工作簿，但我对简单地复制整个工作表并将其粘贴到新工作表感兴趣 - 在同一个工作簿中。

I'm in the process of converting a 2003 .xls file to 2010 .xlsm and the old method used for copying and pasting between worksheets doesn't paste with the correct row heights. My initial workaround was to loop through each row and grab the row heights from the worksheet I am copying from, then loop through and insert those values for the row heights in the worksheet I am pasting to, but the problem with this approach is that the sheet contains buttons which generate new rows which changes the row numbering and the format of the sheet is such that all rows cannot just be one width.

我正在将 2003 .xls 文件转换为 2010 .xlsm 并且用于在工作表之间复制和粘贴的旧方法没有粘贴正确的行高。我最初的解决方法是遍历每一行并从我正在复制的工作表中获取行高，然后遍历并在我粘贴到的工作表中插入行高的这些值，但这种方法的问题是工作表包含生成新行的按钮，这些新行会更改行编号，并且工作表的格式使得所有行不能只是一个宽度。

What I would really like to be able to do is just simply copy the entire worksheet and paste it. Here is the code from the 2003 version:

我真正想做的只是简单地复制整个工作表并粘贴它。以下是 2003 版的代码：

ThisWorkbook.Worksheets("Master").Cells.Copy
newWorksheet.Paste

I'm surprised that converting to .xlsm is causing this to break now. Any suggestions or ideas would be great.

我很惊讶转换为 .xlsm 会导致它现在中断。任何建议或想法都会很棒。

回答by brettdj

It is simpler just to run an exact copy like below to put the copy in as the last sheet

只需运行如下所示的精确副本以将副本作为最后一张纸放入就更简单了

Sub Test()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
End Sub

回答by Jean-Fran?ois Corbett

ThisWorkbook.Worksheets("Master").Sheet1.Cells.Copy _
    Destination:=newWorksheet.Cells

The above will copy the cells. If you really want to duplicate the entire sheet, then I'd go with @brettdj's answer.

以上将复制单元格。如果您真的想复制整个工作表，那么我会选择@brettdj 的回答。

回答by thanos.a

' Assume that the code name the worksheet is Sheet1

' Copy the sheet using code name and put in the end.
' Note: Using the code name lets the user rename the worksheet without breaking the VBA code
Sheet1.Copy After:=Sheets(Sheets.Count)

' Rename the copied sheet keeping the same name and appending a string " copied"
ActiveSheet.Name = Sheet1.Name & " copied"

回答by Kes Perron

I really liked @brettdj's code, but then I found that when I added additional code to edit the copy, it overwrote my original sheet instead. I've tweaked his answer so that further code pointed at ws1will affect the new sheet rather than the original.

我真的很喜欢@brettdj 的代码，但后来我发现当我添加额外的代码来编辑副本时，它反而覆盖了我的原始工作表。我已经调整了他的答案，以便进一步指出的代码ws1将影响新工作表而不是原始工作表。

Sub Test()
    Dim ws1 as Worksheet
    ThisWorkbook.Worksheets("Master").Copy
    Set ws1 = ThisWorkbook.Worksheets("Master (2)")
End Sub

回答by shanxiang shen

'Make the excel file that runs the software the active workbook
ThisWorkbook.Activate

'The first sheet used as a temporary place to hold the data 
ThisWorkbook.Worksheets(1).Cells.Copy

'Create a new Excel workbook
Dim NewCaseFile As Workbook
Dim strFileName As String

Set NewCaseFile = Workbooks.Add
With NewCaseFile
    Sheets(1).Select
    Cells(1, 1).Select
End With

ActiveSheet.Paste

回答by Maccus

If anyone has, like I do, an Estimating workbook with a default number of visible pricing sheets, a Summary and a larger number of hidden and 'protected' worksheets full of sensitive data but may need to create additional visible worksheets to arrive at a proper price, I have variant of the above responses that creates the said visible worksheets based on a protected hidden "Master". I have used the code provided by @/jean-fran%c3%a7ois-corbett and @thanos-a in combination with simple VBA as shown below.

如果有人像我一样，有一个估算工作簿，其中包含默认数量的可见定价表、摘要和大量隐藏和“受保护”的充满敏感数据的工作表，但可能需要创建额外的可见工作表以得出适当的价格，我有上述响应的变体，它基于受保护的隐藏“主”创建所述可见工作表。我将@/jean-fran%c3%a7ois-corbett 和@thanos-a 提供的代码与简单的 VBA 结合使用，如下所示。

Sub sbInsertWorksheetAfter()

子 sbInsertWorksheetAfter()

    'This adds a new visible worksheet after the last visible worksheet

    ThisWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)

    'This copies the content of the HIDDEN "Master" worksheet to the new VISIBLE ActiveSheet just created

    ThisWorkbook.Sheets("Master").Cells.Copy _
        Destination:=ActiveSheet.Cells

    'This gives the the new ActiveSheet a default name

    With ActiveSheet
        .Name = Sheet12.Name & " copied"
    End With

    'This changes the name of the ActiveSheet to the user's preference

    Dim sheetname As String

    With ActiveSheet
        sheetname = InputBox("Enter name of this Worksheet")
        .Name = sheetname
    End With

End Sub

结束子