excel - How to replicate a sheet using VBA Macro (not copy) replicate

Question

How would I replicate a sheet using VBA Macro but not use the VBA copy method?

So I want Sheet2 to look exactly like Sheet1 after.

I am new to VBA Macros so please guide me.

Answer 1

Here are couple of ways

WAY 1 Best way to do it

ThisWorkbook.Sheets("Sheet1").Copy _
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Way 2

Sub Sample()
    Dim wsToCopy As Worksheet, wsNew As Worksheet

    On Error GoTo Whoa:

    Set wsToCopy = ThisWorkbook.Sheets("Sheet1")
    Set wsNew = ThisWorkbook.Sheets.Add

    wsNew.Name = "Copy of " & wsToCopy.Name

    wsToCopy.Cells.Copy wsNew.Cells

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

NOTE:

In case you are using Excel 2003, then WAY 2 might be the best way depending on the data. Please SEE THIS