Copy Sheet named as current date with button VBA Excel

Try the following work-around:

Enter a value of 1 in cell AA1of the "Operator handover log new" sheet. This cell will be used as a "helper" cell to keep track of the sheet numbers and should never be deleted. Then try this macro:

Private Sub CommandButton1_Click()
    Dim szTodayDate As String
    szTodayDate = Format(Date, "mmm-dd-yyyy")
    Sheets("Operator handover log new").Copy After:=Worksheets(Worksheets.Count)
    ActiveSheet.Range("AA1").ClearContents
    ActiveSheet.Name = szTodayDate & " (" & Sheets("Operator handover log new").Range("AA1") & ")"
    Sheets("Operator handover log new").Range("AA1") = Sheets("Operator handover log new").Range("AA1") + 1
End Sub