Run VBA Search from different tab

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    Wanting multiple rows of results from single cell search | MrExcel Message Board


    Okay, very new to VBA. I have a 3-tab workbook with Cemetery data.

    ABCDEFGHI
    Plot #
    RowAvailableLast Name
    First Name
    BirthDeathInscriptionStone Condition


    The Tabs are: "Search", "Map", "Details"

    I have some VBA (below) that will give me a sorted list on the Details tab (where the code is loaded).

    What I would ultimately like is:

    Run the search from the "Search" tab and have the results on the "Details" tab. Also, have a way to 'clear search' (currently, when you clear out the search field in the Details tab, the filtered list remains.


    Here's the VBA

    --------------------------------------------------------------------

    Private Sub Worksheet_Change(ByVal Target As Range)


    If Intersect(Target, Range("L1")) Is Nothing Then Exit Sub

    If Target.Count > 1 Then Exit Sub

    If Target.Value = vbNullString Then Exit Sub


    Application.ScreenUpdating = False


    With Me.[A1].CurrentRegion

    .AutoFilter 4, Target.Value

    End With


    Application.ScreenUpdating = True


    End Sub

    ---------------------------------------------------------------

    Any help is most appreciated.

    Mike

  • alansidman

    Added the Label Cross Post
  • Cross posted with MrExcel who provided the following solution:



    VBA Code:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Intersect(Target, Range("J1")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Target.Value = vbNullString Then Exit Sub
    
    Application.ScreenUpdating = False
            Sheet2.UsedRange.Offset(1).Clear                With Me.[A1].CurrentRegion                .AutoFilter 3, Target.Value                .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)                .AutoFilter        End With                Sheet2.Columns.AutoFit                Application.ScreenUpdating = True
    
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!