Sub Auto_Open() - Sheet name - error code

  • Thread starter Thread starter transferxxx
  • Start date Start date
T

transferxxx

Sub Auto_Open()
Dim SH As Worksheet
Set SH = Worksheets(Worksheets.Count)
Worksheets.Add after:=SH
Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd")

End Sub

What additional code do i need to add & where - in order not to get an
error message if sheet name already exists when opening file a second
time

Thxs
 
Check the name of that sheet before you try to insert another.
If SH.Name=Format(Date, "yyyymmdd") Then
'Decide what to do

NickHK
 
i new to excel vba - can you give me the complete code which would
leave the workbook unchanged if a sheet already exist with todays date
thxs
 
Hi Transfer,

Further to Nick's suggestion, in case the sheet might be present anywhere in
the workbook, try:

'=============>>
Sub Auto_Open()
Dim SH As Worksheet
Dim sStr As String
Dim blExists As Boolean

sStr = Format(Date, "yyyymmdd")

Set SH = Worksheets(Worksheets.Count)

On Error Resume Next
blExists = CBool(Len(Sheets(sStr).Name))
On Error GoTo 0

If blExists Then
'Sheet already exists,do something, e.g.:
MsgBox "Sheet " & sStr & " already exists!"
Else
Worksheets.Add after:=SH
Worksheets(Worksheets.Count).Name = sStr
End If
End Sub
'<<=============
 
If you exit the sub, there is no "Else" part.
But if you mean :

If SH.Name=Format(Date, "yyyymmdd") Then
'Sheet exists already, so do something
Else
Worksheets.Add after:=SH
Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd")
End If
'Continue with other code
...... etc

Seems like you do not want to do anything if the sheet exists already.

NickHK
 
Norman,
I was thinking I should expand it as you have, but I was being a bit lazy in
answering the OP's question and sticking to the requested situation.

NickHK
 
you are right - do not want to do anything if the sheet exists already
I guess the code below should work?

Sub Auto_Open()
Dim SH As Worksheet
If Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd") Then
Exit Sub
Set SH = Worksheets(Worksheets.Count)
Worksheets.Add after:=SH
Worksheets(Worksheets.Count).Name = Format(Date, "yyyymmdd")

End Sub
 
Hi,

Or like that
Sub Auto_Open()
Dim wk As Worksheet
Dim sStr As String

sStr = Format(Date, "yyyymmdd")
On Error Resume Next
Set wk = Worksheets(sStr)
On Error GoTo 0

If wk Is Nothing Then
Set wk = Worksheets.Add(after:=Worksheets.Count)
wk.Name = sStr
End If
Set wk = Nothing
End Sub
Regards
Jean-Yves
 
Back
Top