VBA - copy and paste worksheet with new name

[emma1234]

Hello

Can anyone please point me in the right direction with some VBA?

I want to copy and paste a worksheet ("Phase2_Timeline") onto a new worksheet (that hasn't yet been created), but for it to be called "TU Report " & Format(Date, "dd-mmm-yy").

I have been trying;

Sub Create_TUReport()

Sheets("Phase2_Timeline").Select
Sheets("Phase2_Timeline").Copy After:=Sheets(1)

Set wsNew = Sheets(Sheets("Sheet3").Index + 1)
wsNew.Name = "TU Report " & Format(Date, "dd-mmm-yy")


But this isn't working. Any ideas?

Thanks so much

 

[My Aswer Is This]

Try this:

Sub Create_TUReport()
'Modified  5/5/2019  6:00:50 PM  EDT
On Error GoTo M
Sheets("Phase2_Timeline").Copy After:=Sheets(1)
ActiveSheet.Name = "TU Report " & Format(Date, "dd-mmm-yy")
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "That sheet name may already exist"
End Sub