VBA Check if Sheet Exists, if it does, run code. Test File Attached

**unit285** · 01-13-2016, 11:30 AM

Search Results Test.xlsm

Hello, I'm a VBA Novice, (Trying to learn) and have the following issue, I'm trying to create a Search Function which will search data on my ("Database1") sheet and copy it to a new sheet called ("Search-Results"). Currently my code checks to see if ("Search-Results") exists, if it does not, it then creates it and pastes the data to it. This part of my code is working exactly as I want. As soon as ("Search-Results") is created it switches to it and shows the user the results of the search they just ran. My problems start when ("Search-Results") already exists. So when ("Search-Results") already exists, the code should then simply skip the sheet creation line of code and do everything else the same, but I am having trouble with achieving this. Atm, i have an IF MsgBox line that states "One or More Matches Found!" if the user's search has matches. I would like this MsgBox to not be there. The reason I dont simply delete it is that when I do it breaks the code that checks if the sheet already exists and it trys to create a 2nd sheet called ("Search-Results") which ends in failure. So to best demonstrate whet I would like to achieve, plz download the attached copy and click the "Enter Data" button which brings up my Search Userform, Input "Allen" as a Search and you will see the code creates ("Search-Results") and shows the results found for "Allen". (This is the part that works) Then input "John" and hit Search again and you will see the difference. All help is greatly appreciated!

**Norie** · 01-13-2016, 11:42 AM

You could use this right at the start to check for the 'Search-Results' sheet.

    On Error Resume Next
    Set wsResults = Sheets("Search-Results")
    On Error GoTo 0
    
    If wsResults Is Nothing Then
        Set wsResults = Sheets.Add
        wsResults.Name = "Search-Results"
    End If

**unit285** · 01-13-2016, 01:13 PM

If wsResults Is Nothing Then

Above is the line that gave me an error, and it was when the sheet did not exist. Not sure what error trapping is sorry.. im very new to vba

**unit285** · 01-13-2016, 12:51 PM

Thanks for your reply!
When I try to run that code as stand alone it gives me a debug error.

**Norie** · 01-13-2016, 01:05 PM

Where do you get the error?

Do you get the error when the 'Search-Results' sheet exists?

What level do you have error trapping set at?

**unit285** · 01-13-2016, 01:23 PM

Dim cell As Range
Dim MyRange As Range
            Worksheets("Database1").Activate
     ActiveSheet.Range("$I$1").Value = Searchinput.Value
With Intersect(ActiveSheet.UsedRange, Columns("A:H"))
        .Offset(1, 9).ClearContents
    End With
Set MyRange = Sheets("Database1").Range("A2:H2" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each cell In MyRange
If InStr(cell, Range("I1")) > 0 Then
    i = cell.Row
    Range(Cells(i, 1), Cells(i, 8)).Copy
    Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    End If
Next
Application.CutCopyMode = False
Sheets("Database1").Range("$I$1").ClearContents
Range("$A$1").Select
Sheets("Database1").Range("$J:$Q").Copy Destination:=Sheets("Search-Results").Range("$A$1")
If Sheets("Search-Results").Range("$A$2").Value = 0 Then
    Application.DisplayAlerts = False
    Sheets("Search-Results").Delete
    Application.DisplayAlerts = True
    If MsgBox("No Matches Found!", vbOK, "ATTENTION!") = vbOK Then Exit Sub
End If
Worksheets("Search-Results").Activate
Range("$A$1").Select

The code above works great stand alone and obviously only works if the Sheet "Search-Results" already exists

The code below works great stand alone and obviously only works if the Sheet "Search-Results" does NOT exist.

I need to bring these 2 codes together and make them work as one

Dim cell As Range
Dim MyRange As Range
Dim CS As Integer, BLNFound As Boolean
    BLNFound = False
    With ThisWorkbook
        If BLNFound = False Then
Worksheets("Database1").Activate
     ActiveSheet.Range("$I$1").Value = Searchinput.Value
With Intersect(ActiveSheet.UsedRange, Columns("A:H"))
        .Offset(, 9).ClearContents
    End With
Set MyRange = Sheets("Database1").Range("A2:H2" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each cell In MyRange
If InStr(cell, Range("I1")) > 0 Then
    i = cell.Row
    Range(Cells(i, 1), Cells(i, 8)).Copy
    Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    End If
Next
Application.CutCopyMode = False
Sheets("Database1").Range("$I$1").ClearContents
Range("$A$1").Select
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Search-Results"
Sheets("Database1").Range("$J:$Q").Copy Destination:=Sheets("Search-Results").Range("$A$1")
If Sheets("Search-Results").Range("$A$2").Value = 0 Then
    Application.DisplayAlerts = False
    Sheets("Search-Results").Delete
    Application.DisplayAlerts = True
    If MsgBox("No Matches Found!", vbOK, "ATTENTION!") = vbOK Then Exit Sub
End If
        End If
    End With

**unit285** · 01-14-2016, 11:46 AM

Originally Posted by unit285

Dim cell As Range
Dim MyRange As Range
            Worksheets("Database1").Activate
     ActiveSheet.Range("$I$1").Value = Searchinput.Value
With Intersect(ActiveSheet.UsedRange, Columns("A:H"))
        .Offset(1, 9).ClearContents
    End With
Set MyRange = Sheets("Database1").Range("A2:H2" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each cell In MyRange
If InStr(cell, Range("I1")) > 0 Then
    i = cell.Row
    Range(Cells(i, 1), Cells(i, 8)).Copy
    Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    End If
Next
Application.CutCopyMode = False
Sheets("Database1").Range("$I$1").ClearContents
Range("$A$1").Select
Sheets("Database1").Range("$J:$Q").Copy Destination:=Sheets("Search-Results").Range("$A$1")
If Sheets("Search-Results").Range("$A$2").Value = 0 Then
    Application.DisplayAlerts = False
    Sheets("Search-Results").Delete
    Application.DisplayAlerts = True
    If MsgBox("No Matches Found!", vbOK, "ATTENTION!") = vbOK Then Exit Sub
End If
Worksheets("Search-Results").Activate
Range("$A$1").Select

The code above works great stand alone and obviously only works if the Sheet "Search-Results" already exists

The code below works great stand alone and obviously only works if the Sheet "Search-Results" does NOT exist.

I need to bring these 2 codes together and make them work as one

Dim cell As Range
Dim MyRange As Range
Dim CS As Integer, BLNFound As Boolean
    BLNFound = False
    With ThisWorkbook
        If BLNFound = False Then
Worksheets("Database1").Activate
     ActiveSheet.Range("$I$1").Value = Searchinput.Value
With Intersect(ActiveSheet.UsedRange, Columns("A:H"))
        .Offset(, 9).ClearContents
    End With
Set MyRange = Sheets("Database1").Range("A2:H2" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each cell In MyRange
If InStr(cell, Range("I1")) > 0 Then
    i = cell.Row
    Range(Cells(i, 1), Cells(i, 8)).Copy
    Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    End If
Next
Application.CutCopyMode = False
Sheets("Database1").Range("$I$1").ClearContents
Range("$A$1").Select
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Search-Results"
Sheets("Database1").Range("$J:$Q").Copy Destination:=Sheets("Search-Results").Range("$A$1")
If Sheets("Search-Results").Range("$A$2").Value = 0 Then
    Application.DisplayAlerts = False
    Sheets("Search-Results").Delete
    Application.DisplayAlerts = True
    If MsgBox("No Matches Found!", vbOK, "ATTENTION!") = vbOK Then Exit Sub
End If
        End If
    End With

Is there anyone who can help me put these 2 codes together and make them work as 1 macro?

**Norie** · 01-13-2016, 01:39 PM

How exactly did you try what I suggested and what was the error message?

**unit285** · 01-13-2016, 02:06 PM

http://i.imgur.com/vboq6Ua.png
http://i.imgur.com/IfLpAwv.png

**unit285** · 01-14-2016, 01:23 PM

Was able to stitch it together myself. Thanks for your efforts Norie

Private Sub Find_Click()
For SC = 1 To Worksheets.Count
    If Worksheets(SC).Name = "Search-Results" Then
        exists = True
Dim cell As Range
Dim MyRange As Range
            Worksheets("Database1").Activate
     ActiveSheet.Range("$I$1").Value = Searchinput.Value
With Intersect(ActiveSheet.UsedRange, Columns("A:H"))
        .Offset(1, 9).ClearContents
    End With
Set MyRange = Sheets("Database1").Range("A2:H2" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each cell In MyRange
If InStr(cell, Range("I1")) > 0 Then
    i = cell.Row
    Range(Cells(i, 1), Cells(i, 8)).Copy
    Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    End If
Next
Application.CutCopyMode = False
Sheets("Database1").Range("$I$1").ClearContents
Range("$A$1").Select
Sheets("Database1").Range("$J:$Q").Copy Destination:=Sheets("Search-Results").Range("$A$1")
If Sheets("Search-Results").Range("$A$2").Value = 0 Then
    Application.DisplayAlerts = False
    Sheets("Search-Results").Delete
    Application.DisplayAlerts = True
    If MsgBox("No Matches Found!", vbOK, "ATTENTION!") = vbOK Then Exit Sub
End If
Worksheets("Search-Results").Activate
Range("$A$1").Select
    End If
Next SC
If Not exists Then
Dim CS As Integer, BLNFound As Boolean
    BLNFound = False
    With ThisWorkbook
        If BLNFound = False Then
Worksheets("Database1").Activate
     ActiveSheet.Range("$I$1").Value = Searchinput.Value
With Intersect(ActiveSheet.UsedRange, Columns("A:H"))
        .Offset(, 9).ClearContents
    End With
Set MyRange = Sheets("Database1").Range("A2:H2" & Cells(Rows.Count, 1).End(xlUp).Row)
For Each cell In MyRange
If InStr(cell, Range("I1")) > 0 Then
    i = cell.Row
    Range(Cells(i, 1), Cells(i, 8)).Copy
    Range("J" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    End If
Next
Application.CutCopyMode = False
Sheets("Database1").Range("$I$1").ClearContents
Range("$A$1").Select
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Search-Results"
Sheets("Database1").Range("$J:$Q").Copy Destination:=Sheets("Search-Results").Range("$A$1")
If Sheets("Search-Results").Range("$A$2").Value = 0 Then
    Application.DisplayAlerts = False
    Sheets("Search-Results").Delete
    Application.DisplayAlerts = True
    If MsgBox("No Matches Found!", vbOK, "ATTENTION!") = vbOK Then Exit Sub
End If
        End If
    End With
End If
End Sub

VBA Check if Sheet Exists, if it does, run code. Test File Attached

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Check if Sheet Exists, if it does, run code. Test File Attached

Re: VBA Check if Sheet Exists, if it does, run code. Test File Attached

Re: VBA Check if Sheet Exists, if it does, run code. Test File Attached

Re: VBA Check if Sheet Exists, if it does, run code. Test File Attached

Re: VBA Check if Sheet Exists, if it does, run code. Test File Attached

Re: VBA Check if Sheet Exists, if it does, run code. Test File Attached

Re: VBA Check if Sheet Exists, if it does, run code. Test File Attached

Re: VBA Check if Sheet Exists, if it does, run code. Test File Attached

Re: VBA Check if Sheet Exists, if it does, run code. Test File Attached

Re: VBA Check if Sheet Exists, if it does, run code. Test File Attached

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA - How can I test if a certain sheet already exists?

Need code to check if file exists then activate conditional macro

VBA code to check #REF error exists in Excel file

How to programmatically test whether VBA code exists in an Excel file

[SOLVED] Test if file exists even if hidden

Test if sheet exists

How can I test whether or not an external file exists?

Bookmarks

Bookmarks

Posting Permissions