How to? Enter a value, lookup in another sheet, and if it's a match, then copy the entire row to another sheet

[rusdiculek]

Basically, what I'm trying to figure out is a way to generate the customer name, address, and their bills using a single identity, which is invoice number.

I have the master data which looks like this (Lets say this is Sheet1):

Invoice Number	NAME	ADDRESS	WITEL	BILLS TOTAL
800317164070002000000	NOER SOELISTIONO	RAWA KUNING RT.011/07 NO. 1, PULO GEBANG(KEC.CAKUNG) JAKARTA TIMUR 13950	Jakarta Timur	214500
800322994200002201707	SURYATI	CILENDEK INDAH 6 NO. 10, CILENDEK BARAT KEC.BOGOR BARAT BOGOR 16112	Bogor	151800
800318303240003201607	JOKO SOEMITRO	BASOKA NO. 6, SUMUR BATU JAKARTA PUSAT. 10640	Jakarta Pusat	319000
800323140690001201701	BUDI SETIAWAN	SWAKARSA 1 NO. 13 RT.03/03, PONDOK KELAPA(KEC.DURENSAWIT) JAKARTA TIMUR 13450	Jakarta Timur	368500
800311040960001201611	AGITA ADELIA	CLUSTER VOLENDAM PIK NO. 36, KAPUK MUARA JAKARTA UTARA. 14460	Jakarta Barat	113300
800326117570001201610	H.AGUS TRISNOHADI	KOMPLEK BERMIS NO. BL A1/17, PLUIT JAKARTA UTARA. 14450	Jakarta Utara	412500
88802473175000100000201703	ADI SUWITO TIRTANA	KOMPLEK PURI GARDENA BL B2/32, PEGADUNGAN CKR JAKARTA BARAT. 11830	Jakarta Barat	19203
800320471380003201605	MIRNA	SADAR RAYA NO. 17, JAGAKARSA JAKARTA SELATAN 12620	Jakarta Selatan	385000

<tbody>
</tbody>

and so on...

And I also have a pile of random invoice papers in which the data has been included in master data above. And what I need to do is make an input cell (or macros will be better) where I can input the invoice number which is written in the paper and when I click "OK", the corresponding cell and the whole row will be copied and pasted from master data (Sheet1) to New Sheet (Sheet2).

I found this formula on the internet and it almost solve my problem:

Option Explicit
Public Sub Main()
    Dim intLastColumn As Integer
    Dim wksSheetNew As Worksheet
    Dim wksSheet As Worksheet
    Dim lngLastRow As Long
    Dim strFound As String
    Dim rngRange As Range
    Dim strLink As String
    Dim strTMP As String
    On Error GoTo Fin
    Application.DisplayAlerts = False
    For Each wksSheet In ThisWorkbook.Worksheets
        If wksSheet.Name Like "Found_*" Then
            wksSheet.Delete
        End If
    Next wksSheet
    'strFound = "Laptops"
    strFound = InputBox("Enter search term!", "Search", "Laptops")
    If Trim(strFound) = "" Then Exit Sub
    Set wksSheetNew = Worksheets.Add(Before:=Sheets(1))
    wksSheetNew.Name = "Found_" & Format(Now, "dd_mm_yy_hh_mm_ss")
    lngLastRow = 1
    For Each wksSheet In ThisWorkbook.Worksheets
        If wksSheet.Name <> wksSheetNew.Name Then
            Set rngRange = wksSheet.Columns(2).Find(What:=strFound, _
                LookIn:=xlValues, LookAt:=xlPart)
            If rngRange Is Nothing Then
            Else
                strLink = rngRange.Value
            End If
            If Not rngRange Is Nothing Then
                strTMP = rngRange.Address
                Do
                    lngLastRow = lngLastRow + 1
                    wksSheet.Cells(rngRange.Row, rngRange.Column).EntireRow.Copy _
                        Destination:=wksSheetNew.Cells(lngLastRow, 1)
                    intLastColumn = Cells(lngLastRow, Columns.Count).End(xlToLeft).Column + 1
                    Cells(lngLastRow, intLastColumn).Value = "Sheet"
                    wksSheetNew.Hyperlinks.Add Anchor:=wksSheetNew.Cells _
                        (lngLastRow, intLastColumn), Address:="", _
                        SubAddress:=wksSheet.Name & "!" & rngRange.Address, _
                        TextToDisplay:=wksSheet.Name
                    Set rngRange = wksSheet.Columns(2).FindNext(rngRange)
                Loop While rngRange.Address <> strTMP
                wksSheetNew.Cells.EntireColumn.AutoFit
            End If
        End If
    Next wksSheet
Fin:
    Application.DisplayAlerts = True
    If Err.Number <> 0 Then MsgBox "Error: " & _
        Err.Number & " " & Err.Description
    If strTMP = "" Then
        For Each wksSheet In ThisWorkbook.Worksheets
            If wksSheet.Name Like "Found_*" Then
                wksSheet.Delete
            End If
        Next wksSheet
        MsgBox "Search term was not found!"
    Else
        MsgBox "All matching data has been copied."
    End If
    Set rngRange = Nothing
    Set wksSheetNew = Nothing
End Sub

The remains problem is, however if I want to search something else, it replaces what it already found with my new search. I want to be able to search multiple things and have them pasted on one spreadsheet. (my friend told me there is something wrong with the loop but can't solve it)

Any clues from Excel Pros here would be appreciated. Thank you!!

 

[Legacy 436357]

Why not a formula?

=IFERROR(INDEX(Sheet1!C$3:C$500,MATCH($C4,Sheet1!$B$3:$B$500,0)),"")