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

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
--
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

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" wrote:

> 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
> --
> 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

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

--
If this post helps click Yes
---------------
Jacob Skaria


"ram" wrote:

> 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" wrote:
>
> > 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
> > --
> > 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

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



"Jacob Skaria" wrote:

> 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
>
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "ram" wrote:
>
> > 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" wrote:
> >
> > > 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
> > > --
> > > 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