Check for existing sheet in book
 

Hi,

Wondering if anyone can help me out. I have a button on a template
worksheet that when clicked, copies the template and renames it using
the contents of a ComboBox (cmbSelectYear). Code below:


Private Sub CommandButton1_Click()
Dim StrName As String

StrName = cmbSelectYear

Worksheets("Template").Copy Worksheets(1)
ActiveSheet.Name = StrName

End Sub
===========================================

I need to add code to this same click event that will check for the
existence of a sheet with the same name before copying and renaming. If
a sheet doesnt exist to thencontinue normally, otherwise to exit the
sub.

Can anybody help me, or point me in the right direction?
Any help is appreciated.
Ben


--
benjammind
------------------------------------------------------------------------
benjammind's Profile: http://www.excelforum.com/member.php...o&userid=18217
View this thread: http://www.excelforum.com/showthread...hreadid=382450

Check for existing sheet in book
 

Private Sub CommandButton1_Click()
Dim StrName As String
Dim sh as Worksheet

StrName = cmbSelectYear
On Error Resume Next
set sh = Worksheets(Strname)
On Error go 0
if sh is nothing then
Worksheets("Template").Copy Worksheets(1)
ActiveSheet.Name = StrName
Else
msgbox StrName & " already exists"
End if
End Sub

--
Regards,
Tom Ogilvy

"benjammind" wrote
in message ...

Hi,

Wondering if anyone can help me out. I have a button on a template
worksheet that when clicked, copies the template and renames it using
the contents of a ComboBox (cmbSelectYear). Code below:


Private Sub CommandButton1_Click()
Dim StrName As String

StrName = cmbSelectYear

Worksheets("Template").Copy Worksheets(1)
ActiveSheet.Name = StrName

End Sub
===========================================

I need to add code to this same click event that will check for the
existence of a sheet with the same name before copying and renaming. If
a sheet doesnt exist to thencontinue normally, otherwise to exit the
sub.

Can anybody help me, or point me in the right direction?
Any help is appreciated.
Ben


--
benjammind
------------------------------------------------------------------------
benjammind's Profile:
http://www.excelforum.com/member.php...o&userid=18217
View this thread: http://www.excelforum.com/showthread...hreadid=382450

Check for existing sheet in book
 

Thank you very much for your assistance Tom, that works perfectly.

Ben


--
benjammind
------------------------------------------------------------------------
benjammind's Profile: http://www.excelforum.com/member.php...o&userid=18217
View this thread: http://www.excelforum.com/showthread...hreadid=382450