View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Alan Alan is offline
external usenet poster
 
Posts: 492
Default macro to: Add new sheet, then rename new sheet with todays date

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.
"Paul" wrote in message
...
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 !