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

EugeneW

New Member
Joined
Dec 19, 2022
Messages
1
Platform
  1. Windows
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...

VBA Code:
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
maybe (untested)
VBA Code:
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
 
Upvote 0
Hi EugeneW,

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

Try

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top