Select the sheet1 tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out.

In Sheet 1 cell A1 holds the employee number
Row2 is header in ColA.B.C

Try and feedback

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngRow As Long, lngCount As Long, ws As Worksheet
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Range("A3:D100").ClearContents
If Target.Text < "" Then
Set ws = Worksheets("Sheet2")
For lngRow = 1 To ws.Cells(Rows.Count, "A").End(xlUp).Row
If ws.Range("B" & lngRow) = Range("A1") Then
Range("A" & lngCount + 3) = ws.Range("A" & lngRow)
Range("B" & lngCount + 3) = ws.Range("C" & lngRow)
Range("C" & lngCount + 3) = ws.Range("D" & lngRow)
lngCount = lngCount + 1
End If
Next
End If
End If
Application.EnableEvents = True
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"ram" wrote:

HI,

I would like help with the following question:

I have 2 sheets 1 and 2. On sheet1 in cell A1 the employee number is
entered. below the employee number there is a summary table that is populated
with the following information from sheet2 Date, Report Number, Sales Total.

I would like help creating code that would copy the date, Report Number ,
Sales Total from sheet2 over to Sheet1, when the employee number matches in
each table. sheet2 has 20K rows of data each employee number can have more
that one report for each month.

Sheet 1
Employee No. 1111

Date Report No. Sales Total
1/1/2009
2/1/2009
3/1/2009
4/1/2009


Sheet2
Date EmployeeNo. Report No. Sales Total
1/1/2009 1111 1 100
1/1/2009 2222 1 200
2/1/2009 333 1 300
3/1/2009 456 400
4/1/2009 1111 2 70
4/1/2009 1111 3 80
4/1/2009 1111 4 70


Sheet 1 End Result
Date Report No. Sales Total
1/1/2009 1 300
4/1/2009 2 70
4/1/2009 3 80
4/1/2009 4 70




Thanks in advance for any help