Search sheet2 for value in sheet1 and output rows to sheet1

[tweedledum]

Hi
I need a macro to search for a value(this will be in A1 in sheet1) in sheet2 and output the first five rows with this value into sheet1. The numbers i am looking for are in column A in sheet2. so i want to search columnn A Sheet2 and output the first 5 rows that correspond with this value into sheet1 (say A6 to A11) the other columns contain comments, the number in A1sheet1 is a customer code there could be ten comments with this customer code or 200!!! this number (in A1 ) could change as its a vlookup from a data validation listbox, so when the supplier changes this number changes and the comments related to that customer wil come up so i need to be able to delete the old comments and replace them with the new ones in sheet1 everytime the number changes.
Can anyone help i am a little lost?? thanks in advance

 

[jbeaucaire]

Give this macro a try.

Sub AGet5Matches()
Dim lastrow As Long, rng As Range, 
Dim Key1 As Variant, cell As Range

lastrow = Sheets("Sheet2").UsedRange.Rows.Count
Key1 = Sheets("Sheet1").Range("A1").Value
Set rng = Sheets("Sheet2").Range("A2:A" & lastrow)

    Sheets("Sheet1").Range("A6:Z10").ClearContents

    For Each cell In rng
        If Sheets("Sheet1").Range("A10").Value <> "" Then Exit Sub
        If cell.Value = Key1 Then
        cell.EntireRow.Copy Sheets("Sheet1").Range("A" & Cells(Rows.Count, 1).Row).End(xlUp).Offset(1, 0)
        End If
    Next cell
End Sub