Как после открытия определённой книги, сделать так, чтобы имя нужного рабочего листа = текущей дате?

Разместите в модуле ThisWorkbook (ЭтаКнига):

Private Sub Workbook_Open()
    Лист1.Name = Date 'Date$
End Sub

Если структура рабочей книги защищена [FAQ], и стало быть, переименование листов невозможно, то возникнет ошибка, которую можно избежать, если добавить небольшую проверку:

Private Sub Workbook_Open()
    If Not Me.ProtectStructure _
    Then Лист1.Name = Date 'Date$
End Sub

Второй способ. Разместите в в любом стандартном модуле нужной рабочей книги:

Private Sub Auto_Open()
    ThisWorkbook.Worksheets(1).Name = Date 'Date$
End Sub

Тоже самое, но с предварительной проверкой:

Private Sub Auto_Open()
    If Not ThisWorkbook.ProtectStructure Then _
    ThisWorkbook.Worksheets(1).Name = Date 'Date$
End Sub

Если отображение даты в виде ДД.ММ.ГГГГ (или ММ-ДД-ГГГГ) Вас не устраивает, то воспользуйтесь, например, VB функцией Format

ThisWorkbook.Worksheets(1).Name = Format(Now, "Long Date")