Copy Sheet named as current date with button VBA Excel

I am trying to copy the first sheet once/click and name it automatically as current date.

first click is adding 2 sheets (10-Jun-21 & 10-Jun-21 (2));
Second click is adding Operator(2) & 10-Jun-21 (3);
third click is adding Operator(3) & 10-Jun-21(4);
When i click the button 4th time is adding one sheet per click (10-Jun-21(5)).
I don't understand why after the 4th click does what i want it to do. Here is my code. Thank you!!!

Private Sub CommandButton1_Click()

Dim I As Long
Dim szTodayDate As String
    szTodayDate = Format(Date, "mmm-dd-yyyy")
    On Error Resume Next

Sheets("Operator").Copy After:=Worksheets(Worksheets.Count)
Do
ActiveSheet.Name = szTodayDate
    Sheets(szTodayDate).Copy After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = szTodayDate

Loop While ActiveSheet.Name = szTodayDate

End Sub

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Try:

Private Sub CommandButton1_Click()
    Dim I As Long, szTodayDate As String
    szTodayDate = Format(Date, "mmm-dd-yyyy")
    If Not Evaluate("isref('" & szTodayDate & "'!A1)") Then
        Sheets("Operator").Copy After:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = szTodayDate
    Else
        Sheets(szTodayDate).Copy After:=Worksheets(Worksheets.Count)
    End If
End Sub

You are very welcome.

It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific sheets (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

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

My pleasure.