| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and past based on criteria
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy and past based on criteria
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy and past based on criteria
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("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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy and past based on criteria
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("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) 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("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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy and past based on criteria
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("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) 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("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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy Cells Based on Criteria in VBA | Excel Programming | |||
| Copy and pasting based on criteria | Excel Programming | |||
| Copy Row to worksheet based on criteria | Excel Discussion (Misc queries) | |||
| Filter/copy based on criteria | Excel Programming | |||
| Filter/copy based on criteria | Excel Programming | |||
Linear Mode
