kevinh2320
Board Regular
- Joined
- May 13, 2016
- Messages
- 61
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
<tbody>
</tbody>
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)
<tbody>
</tbody>
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
<tbody>
</tbody>
4. Desired "InDefaultMerge" worksheet shown below:
<tbody>
</tbody>
Thank you for any help you can provide.
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
FILE_TYPE | FILE_NUMBER | CASE_TYPE | SUB_TYPE | OPR | IN_DEFAULT | STATUS | PAY_DUE_DATE | AMT_OWED | ADJ_NOTES |
ABC | 123456 | 1 | 2 | 1 | 35 | 1/1/2019 | |||
ABC | 123457 | 1 | 2 | 1 | Yes | 35 | 1/1/2019 | 1000.00 | Default |
ABC | 123458 | 1 | 2 | 1 | Remove | 35 | 1/1/2019 | Remove | |
ABC | 123459 | 1 | 2 | 1 | Yes | 35 | 1/1/2019 | 1500.00 | Default |
ABC | 123410 | 1 | 2 | 1 | Question | 38 | 1/1/2019 | Question | |
ABC | 123411 | 1 | 2 | 1 | 35 | 1/1/2019 | |||
ABC | 123412 | 1 | 2 | 1 | Yes | 35 | 1/1/2019 | 800.00 | Default |
<tbody>
</tbody>
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)
FILE_TYPE | FILE_NUMBER | CASE_TYPE | SUB_TYPE | OPR | IN_DEFAULT | STATUS | PAY_DUE_DATE | AMT_OWED | ADJ_NOTES |
ABC | 123457 | 1 | 2 | 1 | Yes | 35 | 1/1/2019 | 1000.00 | Default |
ABC | 123459 | 1 | 2 | 1 | Yes | 35 | 1/1/2019 | 1500.00 | Default |
ABC | 123412 | 1 | 2 | 1 | Yes | 35 | 1/1/2019 | 800.00 | Default |
<tbody>
</tbody>
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
FILE_TYPE | FILE_NUMBER | CUST_ID | CUST_NAME | ADDRESS | CITY/ST/ZIP |
ABC | 123456 | 1 | ABC CO | 123 ANY ST. | SEA WA 98022 |
ABC | 123457 | 2 | B&C INC. | 11 1ST AVE | SEA WA 98022 |
ABC | 123458 | 3 | DZIP CO. | 132 22ND | SEA WA 98022 |
ABC | 123459 | 4 | BEVCO | 32 A ST. | SEA WA 98022 |
ABC | 123410 | 5 | PRICECO | 333 22ND | SEA WA 98022 |
ABC | 123411 | 6 | DD SHOP | 55 N. 6TH | SEA WA 98022 |
ABC | 123412 | 7 | BESTB | 92 W. MAPLE | SEA WA 98022 |
ABC | 123413 | 8 | Z COMM | 44 RAIL ST. | SEA WA 98022 |
ABC | 123414 | 9 | TEST CO. | 73 1ST AVE. | SEA WA 98022 |
ABC | 123415 | 10 | BRAND C. | 111 8TH PL. | SEA WA 98022 |
ABC | 123416 | 11 | LEVEL UP | 421 6TH AVE. | SEA WA 98022 |
<tbody>
</tbody>
4. Desired "InDefaultMerge" worksheet shown below:
FILE_TYPE | FILE_NUMBER | CASE_TYPE | SUB_TYPE | OPR | IN_DEFAULT | STATUS | PAY_DUE_DATE | AMT_OWED | ADJ_NOTES | CUST_NAME | ADDRESS | CITY/ST/ZIP |
ABC | 123457 | 1 | 2 | 1 | Yes | 35 | 1/1/2019 | 1000.00 | Default | B&C INC. | 11 1ST AVE | SEA WA 98022 |
ABC | 123459 | 1 | 2 | 1 | Yes | 35 | 1/1/2019 | 1500.00 | Default | BEVCO | 32 A ST. | SEA WA 98022 |
ABC | 123412 | 1 | 2 | 1 | Yes | 35 | 1/1/2019 | 800.00 | Default | BESTB | 92 W. MAPLE | SEA WA 98022 |
<tbody>
</tbody>
Thank you for any help you can provide.