Copy an entire worksheet to a new worksheet in Excel 2010
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.
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:
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.
只需运行如下所示的精确副本即可将副本放入最后一张表格中
Sub Test()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
End Sub
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.
' 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"