VBA error saving a new sheet. "That name is already taken"

[EugeneW]

I'm trying to run a macro that will copy and save the sheet with today's date in its worksheet name. It will work perfectly fine when I run this macro once a day, but it will give back an error when I run it a second time during the same day because I don't have any error handling in it. Ideally, if the macro is ran the 2nd time during the same day, I would like to add "(1)" at the end of the name, or "(2)" if it is the third time and so on. Can someone help me on how to do this? Below is the current macro code which is very simple...

Sub Create_Draft()
' Create copy to new tab
    ActiveSheet.Range("5:150").Copy
    todayDate = Format(Date, "yy-mm")
    Sheets.Add After:=Worksheets("Order")
    ActiveSheet.Name = ("Draft_" & todayDate)
    ActiveSheet.Paste

' Set Password to New Sheet
    ActiveSheet.Protect password:="aaaa"
End Sub

 

[Micron]

maybe (untested)

Sub Create_Draft()
Dim intCount As Integer
Dim sht As Worksheet
' Create copy to new tab
ActiveSheet.Range("5:150").Copy
todayDate = Format(Date, "yy-mm")
For Each sht In ThisWorkbook
   If Instr(sht.Name,todayDate) > 0 Then intCount = intCount + 1
Next
Sheets.Add After:=Worksheets("Order")
If intCount = 0 Then 
   ActiveSheet.Name = ("Draft_" & todayDate)
Else
   ActiveSheet.Name = "Draft_" & todayDate & "_" & intCount
End If
ActiveSheet.Paste

' Set Password to New Sheet
ActiveSheet.Protect password:="aaaa"
End Sub

 

[HaHoBe]

Hi EugeneW,

today's date should not only consist of Year and Month...

Try

Sub Create_Draft_mod()
  Dim strNewName As String
' Create copy to new tab
  strNewName = "Draft_" & Format(Date, "yy-mm-dd")
 
  If Not Evaluate("ISREF('" & strNewName & "'!A1)") Then
    ActiveSheet.Range("5:150").Copy
    Sheets.Add After:=Worksheets("Order")
    With ActiveSheet
      .Name = strNewName
      .Paste
' Set Password to New Sheet
      .Protect Password:="aaaa"
    End With
  End If
End Sub

This will check if a sheet with today's date already exists and only continue if you need to add iit.

Ciao,
Holger