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

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

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

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" <(E-Mail Removed)> wrote in message
news:663E9E06-BE29-49D9-8ADB-(E-Mail Removed)...
> 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