copy and past based on criteria

[ram]

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

 

[Jacob Skaria]

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("A3100").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

 

[ram]

HI Jacob,

I'm not sure if my reply posted the first time.

Your code worked great Thanks !!

I did have one other question is there a way to sum the total sales if the
report number is listed more than once?

Thanks for any help

Ramone

 

[Jacob Skaria]

I didnt..Modified to suit your requirement..The total will be reflected in
cell B1. Adjust to suit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngRow As Long, lngCount As Long, ws As Worksheet
Dim varTotal As Variant
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Range("A3100").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)
varTotal = varTotal + ws.Range("D" & lngRow)
lngCount = lngCount + 1
End If
Next
Range("B1") = varTotal
End If
End If
Application.EnableEvents = True
End Sub

 

[ram]

HI Jacob,

I was looking for something a little differant based on the table shown
below the result would be 150 for January as shown in sheet1 below

Thanks again for your help

Sheet2
Date EmployeeNo. Report No. Sales Total
1/1/2009 1111 1 100
1/1/2009 1111 1 50
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


Sheet1
1111 370
Date Report No. Sales Total
1/1/2009 1 150
4/1/2009 2 70
4/1/2009 3 80
39904 4 70