Check for existing sheet in book

  • Thread starter Thread starter benjammind
  • Start date Start date
B

benjammind

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
 
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
 
Back
Top