macro to: Add new sheet, then rename new sheet with todays date

[Guest]

With workbook already open, would like to have a macro perform:
1) add new sheet
2) rename that new sheet with todays date.

Step 1 easy, completed.
Need guidance on step 2


Thanks !

 

[Paul B]

Paul, something like this,

Worksheets.Add.Name = Format(Date, "mmm dd yy")

Paul B

 

[Guest]

Indeed !! that works, a large Thanks !

Paul, something like this,

Worksheets.Add.Name = Format(Date, "mmm dd yy")

Paul B

 

[Alan]

I don't know if you are familiar with VB so,
Hit Alt and F11 to open the VB editor,
Click 'Insert' > 'Module'
In the empty screen cut and paste this into it:-

Sub NewSheet()
On Error GoTo Error
Dim AddSheet
Sheets.Add
AddSheet = Format(Date, "dd mmm yy")
ActiveSheet.Name = AddSheet
Exit Sub
Error:
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
MsgBox "A Sheet named " & AddSheet & " already exists."
End Sub

Exit the VB editor,
Click 'Views' > 'Toolbars' > 'Forms' > 'Button' to put a cammand button on
the sheet,
Right click the Button, select 'Assign Macro', click 'NewSheet'
Rename the Button and move it where you want it and there you go,
Regards,
Alan.