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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 !
 
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.
 
Back
Top