How to find if a sheet exists?

[Todd_M]

Hi-

For Each s In Worksheets("sheet2").Range("a1:a100")
If s.Value = date_form.Calendar3.Value Then
x = s.Offset(0, 1).Value

If Sheets(x).Value = True Then
Sheets(x).Activate
Else
ActiveWorkbook.Sheets.Add().Name = x.Value
sheets(x).Activate
End If
End If
Next s


In this code x stands for the name of the sheet that the code has to find. If x is not true then I want the code to make up a sheet with the name from x.value. I also need the code to activate the sheet. How can I rewrite this so I dont keep getting an error? Thanks

 

[Jay Petrulis]

Hi Todd,

I used the following routine to run through a list to determine if a sheet with that name exists. If it does exists, the sub does nothing. If it does not exist, the sub adds the sheet with the name in the referenced cell.

The sub makes a function call to the UDF "SheetExists"

'---------------
Public Function SheetExists(sheetname) As Boolean
Dim abc As Object
On Error Resume Next
Set abc = ActiveWorkbook.Sheets(sheetname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

Sub Add_Sheets()
Dim lastrow As Long, sheettoname As String, x As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lastrow = Sheets("Input Sheet").Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To lastrow
sheettoname = Sheets("Input Sheet").Cells(x, 1)
If SheetExists(sheettoname) = True Then
' do nothing
Else
Worksheets.Add After:=Sheets(ThisWorkbook.Worksheets.Count)
Activesheet.Name = Sheets("Input Sheet").Cells(x, 1).Value
Call Module1.SetupNewSheet
End If
Next x
Sheets("Input Sheet").Select
MsgBox "Done!"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
'-----------------

Bye,
Jay

 

[Todd_M]

Hi-
Thanks jay again for your help, Icant seem to figure out where your code would fit into my code that I already have:


Private Sub CommandButton1_Click()
TextBox6.Text = ""
If ComboBox2.Text = "" Then
MsgBox "You must enter a type of payment or 'EXIT'."
Exit Sub
End If
If TextBox4.Text = "" Then
MsgBox "You must enter an amount, or 'EXIT'."
Exit Sub
End If
If TextBox1.Text = "" Then
TextBox1.Text = "N/A"
End If
If TextBox2.Text = "" Then
TextBox2.Text = "N/A"
End If
If ComboBox1.Text = "" Then
ComboBox1.Text = "N/A"
End If
If TextBox3.Text = "" Then
TextBox3.Text = "N/A"
End If
If TextBox5.Text = "" Then
TextBox5.Text = "N/A"
End If

Sheets("sheet2").Select
Worksheets("sheet2").Range("d1").End(xlDown).Offset(0, 0).Select
post.TextBox6.Text = Selection.Value


For Each s In Worksheets("sheet2").Range("a1:a100")
If s.Value = date_form.Calendar3.Value Then
x = s.Offset(0, 1).Value

If Sheets(x).Value = True Then
Sheets(x).Activate

Else
ActiveWorkbook.Sheets.Add().Name = x.Value

Sheets(x).Activate

End If
End If
Next s
nextrow = Application.WorksheetFunction.CountA(Range("a:a")) + 1
Cells(nextrow, 1) = date_form.Calendar3.Value
Cells(nextrow, 2) = TextBox1.Text
Cells(nextrow, 3) = TextBox2.Text
Cells(nextrow, 4) = ComboBox2.Text
Cells(nextrow, 5) = ComboBox1.Text
Cells(nextrow, 6) = TextBox3.Text
Cells(nextrow, 7) = TextBox4.Text
Cells(nextrow, = TextBox5.Text
Cells(nextrow, 9) = TextBox6.Text

Sheets("sheet2").Select
ActiveCell.ClearContents

TextBox1.Text = ""
TextBox2.Text = ""
ComboBox2.Text = ""
ComboBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
End Sub


As you can see I have no idea how to apply it to this code-??
Thanks again Todd