Sub Auto_Open() - Sheet name - error code
 

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

Sub Auto_Open() - Sheet name - error code
 

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

NickHK

wrote in message
ps.com...
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

Sub Auto_Open() - Sheet name - error code
 

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


wrote:
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

Sub Auto_Open() - Sheet name - error code
 

Is this what you mean ?

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

NickHK

wrote in message
oups.com...
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


wrote:
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

Sub Auto_Open() - Sheet name - error code
 

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

thxs


NickHK wrote:
Is this what you mean ?

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

NickHK

wrote in message
oups.com...
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


wrote:
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

Sub Auto_Open() - Sheet name - error code
 

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


---
Regards,
Norman



wrote in message
ps.com...
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

Sub Auto_Open() - Sheet name - error code
 

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

wrote in message
oups.com...
that's it !! - how do i continue with the else command.

thxs


NickHK wrote:
Is this what you mean ?

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

NickHK

wrote in message
oups.com...
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


wrote:
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

Sub Auto_Open() - Sheet name - error code
 

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

"Norman Jones" wrote in message
...
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
'<<=============


---
Regards,
Norman



wrote in message
ps.com...
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

Sub Auto_Open() - Sheet name - error code
 

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






NickHK wrote:
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

wrote in message
oups.com...
that's it !! - how do i continue with the else command.

thxs


NickHK wrote:
Is this what you mean ?

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

NickHK

wrote in message
oups.com...
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


wrote:
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

Sub Auto_Open() - Sheet name - error code
 

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

wrote in message
ps.com...
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