Checking if the sheet already exists
 

Hi,

I have the following code now, Is there a more efficient way to do this ?

For Each sh In ActiveWorkbook.Sheets
counter = 0
If sh.Name = "Singles" Then
MsgBox ("Sheet 'Singles' already exists")
counter = 1
End If
Next

If counter = 0 Then
Set sh = Sheets.Add
sh.Name = "Singles"
End If


Thank you,
--
______
Regards,
Greg

Checking if the sheet already exists
 

Dim sh as Worksheet
On Error Resume Next
set sh = worksheets("Singles")
ON Error goto 0
if sh is nothing then
set sh = worksheets.Add(After:=Worksheets(worksheets.count) )
sh.Name = "Singles"
End if
' now the variable sh refers to Singles in either case

--
Regards,
Tom Ogilvy

"Greg" wrote:

Hi,

I have the following code now, Is there a more efficient way to do this ?

For Each sh In ActiveWorkbook.Sheets
counter = 0
If sh.Name = "Singles" Then
MsgBox ("Sheet 'Singles' already exists")
counter = 1
End If
Next

If counter = 0 Then
Set sh = Sheets.Add
sh.Name = "Singles"
End If


Thank you,
--
______
Regards,
Greg

Checking if the sheet already exists
 

How about this modification? Can I get rid of one of the if statements?
Thanks!


If dataSh Is Nothing Then
shName = Application.InputBox("Enter the name of the data sheet")
Set dataSh = Worksheets(shName)
End If

If dataSh Is Nothing Then
MsgBox ("The name entered does not exist. Exiting...")
Exit Sub
End If



--
______
Regards,
Greg


"Tom Ogilvy" wrote:

Dim sh as Worksheet
On Error Resume Next
set sh = worksheets("Singles")
ON Error goto 0
if sh is nothing then
set sh = worksheets.Add(After:=Worksheets(worksheets.count) )
sh.Name = "Singles"
End if
' now the variable sh refers to Singles in either case

--
Regards,
Tom Ogilvy

"Greg" wrote:

Hi,

I have the following code now, Is there a more efficient way to do this ?

For Each sh In ActiveWorkbook.Sheets
counter = 0
If sh.Name = "Singles" Then
MsgBox ("Sheet 'Singles' already exists")
counter = 1
End If
Next

If counter = 0 Then
Set sh = Sheets.Add
sh.Name = "Singles"
End If


Thank you,
--
______
Regards,
Greg

Checking if the sheet already exists
 

Dim dataSh as Worksheet


shName = Application.InputBox("Enter the name of the data sheet")
On Error Resume Next
Set dataSh = Worksheets(shName)
On Error goto 0

If dataSh Is Nothing Then
MsgBox ("The name entered does not exist. Exiting...")
Exit Sub
End If

--
Regards,
Tom Ogilvy


"Greg" wrote in message
...
How about this modification? Can I get rid of one of the if statements?
Thanks!


If dataSh Is Nothing Then
shName = Application.InputBox("Enter the name of the data sheet")
Set dataSh = Worksheets(shName)
End If

If dataSh Is Nothing Then
MsgBox ("The name entered does not exist. Exiting...")
Exit Sub
End If



--
______
Regards,
Greg


"Tom Ogilvy" wrote:

Dim sh as Worksheet
On Error Resume Next
set sh = worksheets("Singles")
ON Error goto 0
if sh is nothing then
set sh = worksheets.Add(After:=Worksheets(worksheets.count) )
sh.Name = "Singles"
End if
' now the variable sh refers to Singles in either case

--
Regards,
Tom Ogilvy

"Greg" wrote:

Hi,

I have the following code now, Is there a more efficient way to do this
?

For Each sh In ActiveWorkbook.Sheets
counter = 0
If sh.Name = "Singles" Then
MsgBox ("Sheet 'Singles' already exists")
counter = 1
End If
Next

If counter = 0 Then
Set sh = Sheets.Add
sh.Name = "Singles"
End If


Thank you,
--
______
Regards,
Greg