Copy an entire worksheet to a new worksheet in Excel 2010

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   Sub 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  amp    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

User · 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  amp    copied

[vba] Copy an entire worksheet to a new worksheet in Excel 2010

Examples related to vba

Examples related to excel

Examples related to excel-2010