Copy an entire worksheet to a new worksheet in Excel 2010

Solution 1:

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

Solution 2:

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.

Solution 3:

' 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"

Solution 4:

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

Solution 5:

'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