help with vba code to match data on multiple worksheets

[kevinh2320]

I need help with VBA code that will complete the following tasks:

1. On my “Leases” worksheet find all rows with the word “Yes” in the IN_DEFAULT column (column F) and copy those rows A through J.
example "leases" worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]FILE_TYPE[/TD]
[TD]FILE_NUMBER[/TD]
[TD]CASE_TYPE[/TD]
[TD]SUB_TYPE[/TD]
[TD]OPR[/TD]
[TD]IN_DEFAULT[/TD]
[TD]STATUS[/TD]
[TD]PAY_DUE_DATE[/TD]
[TD]AMT_OWED[/TD]
[TD]ADJ_NOTES[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123456[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123457[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD]1000.00[/TD]
[TD]Default[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123458[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Remove[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD][/TD]
[TD]Remove[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123459[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD]1500.00[/TD]
[TD]Default[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123410[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Question[/TD]
[TD]38[/TD]
[TD]1/1/2019[/TD]
[TD][/TD]
[TD]Question[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123411[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123412[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD]800.00[/TD]
[TD]Default[/TD]
[/TR]
</tbody>[/TABLE]

2. Go to the "InDefaultMerge" worksheet and paste the rows found from the "Leases" worksheet into the first available row at cell "A2"
example "InDefaultMerge" worksheet (phase I)
[TABLE="width: 500"]
<tbody>[TR]
[TD]FILE_TYPE[/TD]
[TD]FILE_NUMBER[/TD]
[TD]CASE_TYPE[/TD]
[TD]SUB_TYPE[/TD]
[TD]OPR[/TD]
[TD]IN_DEFAULT[/TD]
[TD]STATUS[/TD]
[TD]PAY_DUE_DATE[/TD]
[TD]AMT_OWED[/TD]
[TD]ADJ_NOTES[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123457[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD]1000.00[/TD]
[TD]Default[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123459[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD]1500.00[/TD]
[TD]Default[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123412[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD]800.00[/TD]
[TD]Default[/TD]
[/TR]
</tbody>[/TABLE]

3. Evaluate the first "FILE_NUMBER" cell in the "InDefaultMerge" worksheet and then go to the "CustInfo" worksheet to look for a matching record. If found copy cells (columns D through F) to the match record (row) in the "InDefaultMerge" worksheet.
example CustInfo worksheet
[TABLE="width: 600"]
<tbody>[TR]
[TD]FILE_TYPE[/TD]
[TD]FILE_NUMBER[/TD]
[TD]CUST_ID[/TD]
[TD]CUST_NAME[/TD]
[TD]ADDRESS[/TD]
[TD]CITY/ST/ZIP[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123456[/TD]
[TD]1[/TD]
[TD]ABC CO[/TD]
[TD]123 ANY ST.[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123457[/TD]
[TD]2[/TD]
[TD]B&C INC.[/TD]
[TD]11 1ST AVE[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123458[/TD]
[TD]3[/TD]
[TD]DZIP CO.[/TD]
[TD]132 22ND[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123459[/TD]
[TD]4[/TD]
[TD]BEVCO[/TD]
[TD]32 A ST.[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123410[/TD]
[TD]5[/TD]
[TD]PRICECO[/TD]
[TD]333 22ND[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123411[/TD]
[TD]6[/TD]
[TD]DD SHOP[/TD]
[TD]55 N. 6TH[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123412[/TD]
[TD]7[/TD]
[TD]BESTB[/TD]
[TD]92 W. MAPLE[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123413[/TD]
[TD]8[/TD]
[TD]Z COMM[/TD]
[TD]44 RAIL ST.[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123414[/TD]
[TD]9[/TD]
[TD]TEST CO.[/TD]
[TD]73 1ST AVE.[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123415[/TD]
[TD]10[/TD]
[TD]BRAND C.[/TD]
[TD]111 8TH PL.[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123416[/TD]
[TD]11[/TD]
[TD]LEVEL UP[/TD]
[TD]421 6TH AVE.[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
</tbody>[/TABLE]

4. Desired "InDefaultMerge" worksheet shown below:
[TABLE="width: 500"]
<tbody>[TR]
[TD]FILE_TYPE[/TD]
[TD]FILE_NUMBER[/TD]
[TD]CASE_TYPE[/TD]
[TD]SUB_TYPE[/TD]
[TD]OPR[/TD]
[TD]IN_DEFAULT[/TD]
[TD]STATUS[/TD]
[TD]PAY_DUE_DATE[/TD]
[TD]AMT_OWED[/TD]
[TD]ADJ_NOTES[/TD]
[TD]CUST_NAME[/TD]
[TD]ADDRESS[/TD]
[TD]CITY/ST/ZIP[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123457[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD]1000.00[/TD]
[TD]Default[/TD]
[TD]B&C INC.[/TD]
[TD]11 1ST AVE[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123459[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD]1500.00[/TD]
[TD]Default[/TD]
[TD]BEVCO[/TD]
[TD]32 A ST.[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]123412[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]Yes[/TD]
[TD]35[/TD]
[TD]1/1/2019[/TD]
[TD]800.00[/TD]
[TD]Default[/TD]
[TD]BESTB[/TD]
[TD]92 W. MAPLE[/TD]
[TD]SEA WA 98022[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for any help you can provide.

 

[mumps]

Try:

Sub CopyRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, desWS2 As Worksheet, fnd As Range, rng As Range
    Set srcWS = Sheets("leases")
    Set desWS = Sheets("InDefaultMerge")
    Set desWS2 = Sheets("CustInfo")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS
        .Range("A1:J" & LastRow).AutoFilter Field:=6, Criteria1:="Yes"
        .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
        .Range("A1").AutoFilter
    End With
    For Each rng In desWS.Range("B2", desWS.Range("B" & desWS.Rows.Count).End(xlUp))
        Set fnd = desWS2.Range("B:B").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            fnd.Offset(, 2).Resize(, 3).Copy desWS.Cells(rng.Row, 11)
            
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[kevinh2320]

Thank you! Works great!

 

[mumps]

You are very welcome.