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):
[TABLE="width: 1845"]
<tbody>[TR]
[TD="class: xl65, width: 221, align: center"]Invoice Number[/TD]
[TD="class: xl65, width: 508, align: center"]NAME[/TD]
[TD="class: xl65, width: 903, align: center"]ADDRESS[/TD]
[TD="class: xl65, width: 109, align: center"]WITEL[/TD]
[TD="class: xl65, width: 104, align: center"]BILLS TOTAL[/TD]
[/TR]
[TR]
[TD="class: xl66"]800317164070002000000[/TD]
[TD="class: xl64"]NOER SOELISTIONO[/TD]
[TD="class: xl64"]RAWA KUNING RT.011/07 NO. 1, PULO GEBANG(KEC.CAKUNG) JAKARTA TIMUR 13950[/TD]
[TD="class: xl64"]Jakarta Timur[/TD]
[TD="class: xl64, align: right"]214500[/TD]
[/TR]
[TR]
[TD="class: xl64"]800322994200002201707[/TD]
[TD="class: xl64"]SURYATI[/TD]
[TD="class: xl64"]CILENDEK INDAH 6 NO. 10, CILENDEK BARAT KEC.BOGOR BARAT BOGOR 16112[/TD]
[TD="class: xl64"]Bogor[/TD]
[TD="class: xl64, align: right"]151800[/TD]
[/TR]
[TR]
[TD="class: xl64"]800318303240003201607[/TD]
[TD="class: xl64"]JOKO SOEMITRO[/TD]
[TD="class: xl64"]BASOKA NO. 6, SUMUR BATU JAKARTA PUSAT. 10640[/TD]
[TD="class: xl64"]Jakarta Pusat[/TD]
[TD="class: xl64, align: right"]319000[/TD]
[/TR]
[TR]
[TD="class: xl64"]800323140690001201701[/TD]
[TD="class: xl64"]BUDI SETIAWAN[/TD]
[TD="class: xl64"]SWAKARSA 1 NO. 13 RT.03/03, PONDOK KELAPA(KEC.DURENSAWIT) JAKARTA TIMUR 13450[/TD]
[TD="class: xl64"]Jakarta Timur[/TD]
[TD="class: xl64, align: right"]368500[/TD]
[/TR]
[TR]
[TD="class: xl64"]800311040960001201611[/TD]
[TD="class: xl64"]AGITA ADELIA[/TD]
[TD="class: xl64"]CLUSTER VOLENDAM PIK NO. 36, KAPUK MUARA JAKARTA UTARA. 14460[/TD]
[TD="class: xl64"]Jakarta Barat[/TD]
[TD="class: xl64, align: right"]113300[/TD]
[/TR]
[TR]
[TD="class: xl64"]800326117570001201610[/TD]
[TD="class: xl64"]H.AGUS TRISNOHADI[/TD]
[TD="class: xl64"]KOMPLEK BERMIS NO. BL A1/17, PLUIT JAKARTA UTARA. 14450[/TD]
[TD="class: xl64"]Jakarta Utara[/TD]
[TD="class: xl64, align: right"]412500[/TD]
[/TR]
[TR]
[TD="class: xl64"]88802473175000100000201703[/TD]
[TD="class: xl64"]ADI SUWITO TIRTANA[/TD]
[TD="class: xl64"]KOMPLEK PURI GARDENA BL B2/32, PEGADUNGAN CKR JAKARTA BARAT. 11830[/TD]
[TD="class: xl64"]Jakarta Barat[/TD]
[TD="class: xl64, align: right"]19203[/TD]
[/TR]
[TR]
[TD="class: xl64"]800320471380003201605[/TD]
[TD="class: xl64"]MIRNA[/TD]
[TD="class: xl64"]SADAR RAYA NO. 17, JAGAKARSA JAKARTA SELATAN 12620[/TD]
[TD="class: xl64"]Jakarta Selatan[/TD]
[TD="class: xl64, align: right"]385000[/TD]
[/TR]
</tbody>[/TABLE]
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)),"")