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:
I'm surprised that converting to .xlsm is causing this to break now. Any suggestions or ideas would be great.
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
Read more... Read less...
' 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"
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
ws1 will affect the new sheet rather than the original.
Sub Test() Dim ws1 as Worksheet ThisWorkbook.Worksheets("Master").Copy Set ws1 = ThisWorkbook.Worksheets("Master (2)") End Sub
'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
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.
'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