insert data from child workbook to master workbook by search matching records

Hi,

I'm a newbie in VBA, search alot of post about how to copy data from child workbook to master workbook by searching matching records without overwritng the existing data.

What i was trying to do is copy all the records from child workbook by searching matching records(company code) from the master workbook. After found the matching records then it will insert the data from child workbook to master workbook.

I was only able to insert the data at the end of the rows. Can anyone help me write marco on how to search matching records then insert the whole row into the matching records.

Sub Merge()
    Dim i As Integer
    Dim Source As Workbook
    Dim Destination As Workbook
    Dim myFileName As String
     
     
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
     
    On Error Resume Next
     
    Set Destination = ThisWorkbook
     
     
    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "C:\Documents and Settings\Administrator\Desktop\test\slave"
        .FileType = msoFileTypeExcelWorkbooks
         
         
        If .Execute > 0 Then 'Workbooks in folder
            For i = 1 To 3 '  Loop through all
                 'Set Paste target first
                myFileName = Dir(.FoundFiles(i))
                
                'Open Souce file
                Set Source = Workbooks.Open(.FoundFiles(i))
                With Destination.Sheets(1)
                     '''''' Here I would then have to make an if to do a different selection based on what input file I have
                    Select Case myFileName
                    Case "Slave1.xls"
                        'Here selection for first file
                    Case "Slave2.xls"
                        'Here selection for second file
                    End Select
                    Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
                End With
                 'Copy and paste to destination
                Source.Sheets(1).Range("A4:D20").Copy Tgt
                 'Close source file without  saving
                Source.Close False
            Next i
             
        End If
         
    End With
     
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.CutCopyMode = False
End Sub

Please help!!!