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.
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.
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