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