Sub Auto_Open() - Sheet name - error code

[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

 

[NickHK]

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

NickHK

 

[transferxxx]

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

 

[NickHK]

Is this what you mean ?

If SH.Name=Format(Date, "yyyymmdd") Then Exit Sub

NickHK

 

[transferxxx]

that's it !! - how do i continue with the else command.

thxs

 

[Norman Jones]

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
'<<=============

 

[NickHK]

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

 

[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

 

[transferxxx]

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

 

[Jean-Yves]

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