VBA Code To Loop Through One Worksheet and Paste Certain Values Into Another

[Jiraya_00]

Hello Everyone,

I need help writing a VBA code that would allow me to loop through a used range of a worksheet and look for the words "No Match" within in a particular column (column I with header name "Match"). If it finds such a value, then I would like to copy data from columns A through H within that row and paste those values onto another worksheet (at the last row of the used range that would keep expanding as more data is being pasted to that worksheet) within the same workbook.

Any help or guidance is much appreciated!

Book5 - Test.xlsx
	A	B	C	D	E	F	G	H	I
1	Num	Name	Rep Assigned	Rep Supervisor	DBD Rep	Original Amt.	Paid Amount	CMA Inv. Amount	Match
2	576916	Customer A - Test Company	Rep A	Rep AB	No Rep	1,723.60	1,723.60	1,723.60	Customer A - Test Company
3	578107	Customer U	Rep AB	Rep AB	No Rep	2,752.80	2,752.80	2,752.80	Customer U
4	580390	Customer A	Rep AB	Rep AB	No Rep	1,016.80	1,016.80	1,016.80	Customer A
5	582249	Customer A	Rep A	Rep AB	No Rep	2,418.00	2,418.00	2,418.00	Customer A
6	583044	Customer A	Rep AB	Rep AB	No Rep	1,822.80	1,822.80	1,822.80	Customer A
7	586326	Customer U	Rep AB	Rep AB	No Rep	2,021.20	2,021.20	2,021.20	Customer U
8	587795	Customer A	Rep AB	Rep AB	No Rep	3,822.45	2,777.60	2,777.60	Customer A
9	587856	Customer A	Rep AB	Rep AB	No Rep	248.00	248.00	248.00	Customer A
10	589448	Customer A	Rep AB	Rep AB	No Rep	1,488.00	1,488.00	1,488.00	Customer A
11	591246	Customer A	Rep A	Rep AB	No Rep	930.00	930.00	930.00	Customer A
12	594666	Customer B	Rep F	Rep AB	No Rep	919.80	919.80	919.80	Customer B
13	595592	Customer B	Rep F	Rep AB	No Rep	1,209.60	1,209.60	1,209.60	NO MATCH
14	596598	Customer B	Rep F	Rep AB	No Rep	415.80	415.80	415.80	Customer B
15	602919	Customer C - Test Company	No Rep	No Rep	Rep BC	882.00	882.00	882.00	Customer C - Test Company
16	605254	Customer C	No Rep	No Rep	Rep BC	1,373.40	1,373.40	1,373.40	Customer C
17	607582	Customer D	Rep F	Rep AB	Rep BC	0.00	0.00	4,158.00	Customer D
18	606435	Customer D	Rep F	Rep AB	Rep BC	0.00	0.00	1,197.00	Customer D
19	607693	Customer F	Rep D	Rep AB	No Rep	1,312.00	1,312.00	1,512.00	Customer F
20	607388	Customer I	Rep A	Rep AB	No Rep	982.80	982.80	982.80	Customer I
21	608791	Customer G	No Rep	No Rep	Rep BC	529.20	529.20	529.20	NO MATCH
22	609018	Customer E	Rep D	Rep AB	Rep BC	2,935.80	2,935.80	2,935.80	Customer E
23	612210	Customer A	Rep C	Rep AB	No Rep	1,423.80	1,423.80	1,423.80	Customer A
24	610144	Customer J	Rep D	Rep BC	No Rep	529.20	529.20	529.20	Customer J
25	610154	Customer J	Rep BC	Rep BC	No Rep	970.20	970.20	970.20	Customer J
26	610856	Customer E - Test Company	Rep D	Rep AB	Rep BC	2,079.00	2,079.00	2,079.00	NO MATCH
27	610418	Customer K	Rep D	Rep AB	Rep CD	997.60	997.60	997.60	Customer K
28	611763	Customer Y	Rep F	Rep BC	Rep CD	5,424.15	1,373.40	1,373.40	Customer Y
29	613097	Customer H	Rep E	Rep AB	Rep CD	1,839.60	1,839.60	1,839.60	Customer H
30	611574	Customer X	Rep D	Rep AB	No Rep	1,751.40	1,751.40	1,751.40	Customer X
31	612467	Customer D	Rep F	Rep AB	Rep BC	1,260.00	1,260.00	1,260.00	Customer D
32	613731	Customer Y	Rep BC	Rep BC	No Rep	1,096.20	1,096.20	1,096.20	Customer Y
33	612406	Customer H	Rep E	Rep AB	Rep CD	365.40	365.40	365.40	NO MATCH
34	613633	Customer C	No Rep	No Rep	Rep BC	1,814.40	1,814.40	1,814.40	Customer C
35	614567	Customer X	Rep BC	Rep BC	No Rep	378.00	378.00	378.00	Customer X
36	612504	Customer D	No Rep	No Rep	Rep BC	1,436.40	1,436.40	1,436.40	Customer D
37	614447	Customer X	Rep D	Rep AB	No Rep	508.40	508.40	508.40	Customer X
38	612224	Customer H	Rep E	Rep AB	Rep CD	2,595.60	2,595.60	2,595.60	Customer H
39	613209	Customer H	Rep E	Rep AB	Rep CD	1,713.60	1,713.60	1,713.60	NO MATCH
40	614454	Customer L	Rep F	Rep BC	No Rep	529.20	529.20	529.20	Customer L
41	613226	Customer T	No Rep	No Rep	Rep CD	0.00	0.00	2,381.40	Customer T
42	613033	Customer T	Rep C	Rep AB	No Rep	2,709.00	2,709.00	2,709.00	Customer T
43	613648	Customer S	Rep C	Rep AB	No Rep	1,131.20	1,131.20	1,131.20	Customer S
44	612981	Customer Z	Rep BC	Rep BC	No Rep	604.80	604.80	604.80	Customer Z
45	613452	Customer H	Rep E	Rep AB	Rep CD	2,091.60	2,091.60	2,091.60	Customer H
46	613856	Customer M	Rep C	Rep AB	Rep CD	359.60	359.60	359.60	Customer M
47	615717	Customer L	Rep D	Rep AB	No Rep	1,486.80	1,486.80	1,486.80	Customer L
48	613765	Customer Z	No Rep	No Rep	Rep BC	831.60	831.60	831.60	Customer Z
49	613862	Customer H	Rep E	Rep AB	Rep CD	1,096.20	1,096.20	1,096.20	NO MATCH
50	615199	Customer T	Rep D	Rep AB	Rep BC	458.80	458.80	458.80	Customer T
51	613368	Customer S	Rep BC	Rep BC	No Rep	1,020.60	1,020.60	1,020.60	Customer S
52	615523	Customer F	Rep C	Rep AB	No Rep	2,308.40	2,308.40	2,308.40	Customer F
53	616648	Customer E	Rep D	Rep AB	Rep BC	1,927.80	1,927.80	1,927.80	Customer E
54	615343	Customer S - Test Company	Rep D	Rep AB	Rep BC	1,289.60	1,289.60	1,289.60	Customer S - Test Company
55	613450	Customer G	No Rep	No Rep	Rep BC	781.20	781.20	781.20	Customer G
56	612849	Customer P	Rep D	Rep AB	No Rep	1,449.00	1,449.00	1,449.00	Customer P
57	618209	Customer Q	No Rep	No Rep	Rep CD	1,524.60	1,524.60	1,524.60	Customer Q
58	613924	Customer Z	Rep BC	Rep BC	No Rep	1,562.40	1,562.40	1,562.40	Customer Z
59	616360	Customer H	Rep E	Rep AB	Rep CD	2,066.40	2,066.40	2,066.40	Customer H
60	615589	Customer L	Rep B	Rep AB	No Rep	2,394.00	2,394.00	2,394.00	Customer L
Source_Data

Book5 - Test.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Report Submission Date	Invoice #	Account Name	Rep Assigned	Rep Supervisor	DBD Rep	ERP Original Amt.	ERP Paid Total	CMA Inv. Amount	ERP - CMA Variance
2	7/27/2023	576916	Customer A - Test Company	Rep A	Rep AB	No Rep	1,723.60	1,723.60	1,723.60	0.00
3	8/3/2023	578107	Customer U	Rep AB	Rep AB	No Rep	2,752.80	2,752.80	2,752.80	0.00
4	8/28/2023	580390	Customer A	Rep AB	Rep AB	No Rep	1,448.42	1,016.80	1,016.80	0.00
5	9/12/2023	582249	Customer A	Rep A	Rep AB	No Rep	2,418.00	2,418.00	2,418.00	0.00
6	9/20/2023	583044	Customer A	Rep AB	Rep AB	No Rep	1,822.80	1,822.80	1,822.80	0.00
7	10/17/2023	586326	Customer U	Rep AB	Rep AB	No Rep	2,021.20	2,021.20	2,021.20	0.00
8	10/31/2023	587795	Customer A	Rep AB	Rep AB	No Rep	3,822.45	2,777.60	2,777.60	0.00
9	11/1/2023	587856	Customer A	Rep AB	Rep AB	No Rep	248.00	248.00	248.00	0.00
10	11/14/2023	589448	Customer A	Rep AB	Rep AB	No Rep	1,488.00	1,488.00	1,488.00	0.00
11	12/7/2023	591246	Customer A	Rep A	Rep AB	No Rep	930.00	930.00	930.00	0.00
12	1/5/2024	594666	Customer B	Rep F	Rep AB	No Rep	919.80	919.80	919.80	0.00
13	1/22/2024	595592	Customer B	Rep F	Rep AB	No Rep	1,209.60	1,209.60	1,209.60	0.00
14	1/27/2024	596598	Customer B	Rep F	Rep AB	No Rep	415.80	415.80	415.80	0.00
15	3/20/2024	602919	Customer C - Test Company	No Rep	No Rep	Rep BC	882.00	882.00	882.00	0.00
16	4/2/2024	605254	Customer C	No Rep	No Rep	Rep BC	1,373.40	1,373.40	1,373.40	0.00
17	4/17/2024	607582	Customer D	Rep F	Rep AB	Rep BC	0.00	0.00	4,158.00	(4,158.00)
18	4/13/2024	606435	Customer D	Rep F	Rep AB	Rep BC	0.00	0.00	1,197.00	(1,197.00)
19	5/2/2024	607693	Customer F	Rep D	Rep AB	No Rep	1,312.00	1,312.00	1,512.00	(200.00)
20	4/30/2024	607388	Customer I	Rep A	Rep AB	No Rep	982.80	982.80	982.80	0.00
21	4/30/2024	608791	Customer G	No Rep	No Rep	Rep BC	529.20	529.20	529.20	0.00
22	5/13/2024	609018	Customer E	Rep D	Rep AB	Rep BC	2,935.80	2,935.80	2,935.80	0.00
23	5/26/2024	612210	Customer A	Rep C	Rep AB	No Rep	1,423.80	1,423.80	1,423.80	0.00
24	5/21/2024	610144	Customer J	Rep D	Rep BC	No Rep	529.20	529.20	529.20	0.00
25	5/23/2024	610154	Customer J	Rep BC	Rep BC	No Rep	970.20	970.20	970.20	0.00
26	5/14/2024	610856	Customer E - Test Company	Rep D	Rep AB	Rep BC	2,079.00	2,079.00	2,079.00	0.00
27	5/27/2024	610418	Customer K	Rep D	Rep AB	Rep CD	1,016.55	997.60	997.60	0.00
28	5/21/2024	611763	Customer Y	Rep F	Rep BC	Rep CD	5,424.15	1,373.40	1,373.40	0.00
29	6/4/2024	613097	Customer H	Rep E	Rep AB	Rep CD	1,839.60	1,839.60	1,839.60	0.00
30	5/22/2024	611574	Customer X	Rep D	Rep AB	No Rep	1,751.40	1,751.40	1,751.40	0.00
31	5/28/2024	612467	Customer D	Rep F	Rep AB	Rep BC	3,360.00	1,260.00	1,260.00	0.00
32	6/14/2024	613731	Customer Y	Rep BC	Rep BC	No Rep	1,096.20	1,096.20	1,096.20	0.00
33	6/12/2024	612406	Customer H	Rep E	Rep AB	Rep CD	788.77	365.40	365.40	0.00
34	6/10/2024	613633	Customer C	No Rep	No Rep	Rep BC	1,814.40	1,814.40	1,814.40	0.00
35	6/21/2024	614567	Customer X	Rep BC	Rep BC	No Rep	378.00	378.00	378.00	0.00
36	5/29/2024	612504	Customer D	No Rep	No Rep	Rep BC	1,436.40	1,436.40	1,436.40	0.00
37	6/13/2024	614447	Customer X	Rep D	Rep AB	No Rep	902.87	508.40	508.40	0.00
38	6/12/2024	612224	Customer H	Rep E	Rep AB	Rep CD	4,695.60	2,595.60	2,595.60	0.00
39	6/5/2024	613209	Customer H	Rep E	Rep AB	Rep CD	2,014.14	1,713.60	1,713.60	0.00
40	6/17/2024	614454	Customer L	Rep F	Rep BC	No Rep	824.69	529.20	529.20	0.00
41	6/19/2024	613226	Customer T	No Rep	No Rep	Rep CD	0.00	0.00	2,381.40	(2,381.40)
42	6/3/2024	613033	Customer T	Rep C	Rep AB	No Rep	2,709.00	2,709.00	2,709.00	0.00
43	6/8/2024	613648	Customer S	Rep C	Rep AB	No Rep	1,131.20	1,131.20	1,131.20	0.00
44	6/3/2024	612981	Customer Z	Rep BC	Rep BC	No Rep	604.80	604.80	604.80	0.00
45	6/10/2024	613452	Customer H	Rep E	Rep AB	Rep CD	2,091.60	2,091.60	2,091.60	0.00
46	6/28/2024	613856	Customer M	Rep C	Rep AB	Rep CD	359.60	359.60	359.60	0.00
47	6/21/2024	615717	Customer L	Rep D	Rep AB	No Rep	1,486.80	1,486.80	1,486.80	0.00
48	6/10/2024	613765	Customer Z	No Rep	No Rep	Rep BC	831.60	831.60	831.60	0.00
49	6/28/2024	613862	Customer H	Rep E	Rep AB	Rep CD	1,096.20	1,096.20	1,096.20	0.00
50	6/27/2024	615199	Customer T	Rep D	Rep AB	Rep BC	855.02	458.80	458.80	0.00
51		610856	Customer E - Test Company	Rep D	Rep AB	Rep BC	2,079.00	2,079.00	2,079.00	0.00
Reconciliation

Cell Formulas
Range	Formula
J2:J51	J2	=H2-I2

 

[Peter_SSs]

This does not loop through the used range but should transfer all the relevant rows at once.
Test with a copy of your workbook.

Sub No_Match()
  Application.ScreenUpdating = False
  With Sheets("Source_Data")
    .AutoFilterMode = False
    With .Range("A1", .Range("I" & Rows.Count).End(xlUp))
      .AutoFilter Field:=9, Criteria1:="NO MATCH"
      If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
        .Offset(1).Resize(.Rows.Count - 1, .Columns.Count - 1).Copy Destination:=Sheets("Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1)
      End If
    End With
    .AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

 

[Jiraya_00]

Thank you Peter! This code works exactly as intended. I do have one question regarding this specific line of code here.

May I ask what does Offset (1) achieve here?

.Offset(1).Resize(.Rows.Count - 1, .Columns.Count - 1).Copy Destination:=Sheets("Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1)

 

[Peter_SSs]

Thank you Peter! This code works exactly as intended.

You are welcome. Thanks for the confirmation.

May I ask what does Offset (1) achieve here?

Do not know which Offset(1) you mean as there are two of them in that line of code.

.Offset(1).Resize(.Rows.Count - 1, .Columns.Count - 1).Copy Destination:=Sheets("Reconciliation").Range("B" & Rows.Count).End(xlUp).Offset(1)

In relation to the purple one:
This moves down one row from the heading row in the source worksheet before copying so that the headings are not copied across to the destination worksheet each time the code is run.

In relation to the red one:
The blue part of the formula locates the last cell with data in column B of the destination worksheet.
If we did not have the red part then the pasted data would over-write the last row of data already in the destination worksheet.
Offset(1) moves down one row (ie to the first available blank row)

 

[Jiraya_00]

Ahhh, got it! Makes sense now Peter...thank you again for all your help on this!

 

[Peter_SSs]

Cheers. No problem.