Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: How to find if a sheet exists?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •