The following code works opening a workbook. It automatically adds a new sheet and labels it with the date. It also checks to see that the sheet doesn’t already exist – to allow for the possibility of it being opened more than once a day.
This code makes use of the Workbook Open Event and must be placed in the workbook module under the “Open work Book” event. The function Sheet_Exist must be placed in a module and this checks whether or not the sheet exists:
Private Sub Workbook_Open()
Dim New_Sheet_Name As String
New_Sheet_Name = Format(Now(), "dd-mm-yy")
If Sheet_Exists(New_Sheet_Name) = False Then
With Workbook
Worksheets.Add().Name = New_Sheet_Name
End With
End If
Save
End Sub
Function Sheet_Exists(WorkSheet_Name As String) As Boolean
Dim Work_sheet As Worksheet
Sheet_Exists = False
For Each Work_sheet In ThisWorkbook.Worksheets
If Work_sheet.Name = WorkSheet_Name Then
Sheet_Exists = True
End If
Next
End Function
To download the .XLSM file for this tutorial, click here