why VBA Run-time error'1004'. you cant paste this here because the copy area and paste area aren't the same size.

[Noni]

In Sheet1, A:D has data about this year’s clients and column E has names of last year’s clients. Sheet2 is where new clients' data need to be copied.

With the below code, I'm trying to paste new 2022 clients' data (A:D) to non-filled cells of Sheet2 in columns A:D. the condition is if A2<>E2:E20 then paste A2:D2 into Sheet2 non-filled row.

but I'm getting run-time error 104: "you can't paste this here because the copy area and paste area aren't the same sizes. Select just one cell in the paste area or an area that's the same size, and try pasting again." and the VBA line "ActiveSheet.Paste" is gets yellow highlighted when I click the command button.
I don't understand why.
Sheet2 shows the desired outcome where green highlighted rows are new clients.

Private Sub CommandButton1_Click()

    c = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    cc = Worksheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row

  For j = 1 To c
    For jj = 1 To cc
      
            If Worksheets("Sheet1").Cells(j, 1).Value <> Worksheets("Sheet1").Cells(jj, 5).Value Then

                Worksheets("Sheet1").Range("a" & i & ":d" & i).Copy

                Worksheets("Sheet2").Activate


                b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

                Worksheets("Sheet2").Cells(b + 1, 1).Select

                ActiveSheet.Paste

                Worksheets("Sheet1").Activate

            End If


        Next: Next

    Application.CutCopyMode = False

    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select


End Sub

Worksheets.xlsm
	A	B	C	D	E
1	2022 Clients	2022 Data	2022 Data2	2022 Data3	2021 Clients
2	Michael	1234	1234	2345	James
3	Sarah	4321	54	67	Sam
4	Mary	9876	0	34	Peter
5	Rachel	5678	12	45	Shaw
6	Anna	7834	23	0	Sally
7	Monica	2356	45	45	Michelle
8	Charles	1234	0	0	Ivona
9	Peter	6543	23	0	Anna
10	Anthony	1234	23	0	Claire
11	Ben	1234	56	34	Ben
12	Elizabeth	6543	0	78	Michael
13	Wong	1276	23456	12	David
14	Sally	9854	23	56	Annaleise
15	Jay	4325	8765	89	Chris
16	Michelle	1265	1256	64	John
17	David	1276	1234	3	Bob
18	Jennifer	1234	8765	56	Anthony
19	Sue	8765	34	78	Sue
20	Ruba	3254	23	9	Robin
21	Henry	9876	78	0
22	Chloe	3245	34567	6
23	Candy	2389	356	5
Sheet1

Worksheets.xlsm
	A	B	C	D
1	2022 Clients	2022 Data	2022 Data2	2022 Data3
2	Peter	6543	23	0
3	Sally	9854	23	56
4	Michelle	1265	1256	64
5	Anna	7834	23	0
6	Ben	1234	56	34
7	Michael	1234	1234	2345
8	David	1276	1234	3
9	Anthony	1234	23	0
10	Sue	8765	34	78
11	Sarah	4321	54	67
12	Mary	9876	0	34
13	Rachel	5678	12	45
14	Monica	2356	45	45
15	Charles	1234	0	0
16	Elizabeth	6543	0	78
17	Wong	1276	23456	12
18	Jay	4325	8765	89
19	Jennifer	1234	8765	56
20	Ruba	3254	23	9
21	Henry	9876	78	0
22	Chloe	3245	34567	6
23	Candy	2389	356	5
Sheet2

 

[Peter_SSs]

Classic example of why, in my opinion, you should always have Option Explicit at the top of every module. You can make that automatically happen by, in the vba window
Tools - Options - Editor tab - Require Variable Declaration.

If you had that set you would not have got as far as this problem.

The problem is that this line Worksheets("Sheet1").Range("a" & i & ":d" & i).Copy is using the variable "i" which is neither declared nor has a value set for it. vba is treating it as Empty and therefore the code line above is effectively this Worksheets("Sheet1").Range("a:d").Copy
That is, it is copying the whole of columns A:D. Since this is copying 1,048,576 rows and you are trying to paste all those rows starting at row 11 (I think) in Sheet 2, you are trying to fit the paste into only 1,048,566 rows - not the same size.

 

[Noni]

Classic example of why, in my opinion, you should always have Option Explicit at the top of every module. You can make that automatically happen by, in the vba window
Tools - Options - Editor tab - Require Variable Declaration.

View attachment 73181

If you had that set you would not have got as far as this problem.

The problem is that this line Worksheets("Sheet1").Range("a" & i & ":d" & i).Copy is using the variable "i" which is neither declared nor has a value set for it. vba is treating it as Empty and therefore the code line above is effectively this Worksheets("Sheet1").Range("a:d").Copy
That is, it is copying the whole of columns A:D. Since this is copying 1,048,576 rows and you are trying to paste all those rows starting at row 11 (I think) in Sheet 2, you are trying to fit the paste into only 1,048,566 rows - not the same size.

@Peter_SSs Thank you so much!!
i've made the changes you recommended. now I'm getting error "Run time error 1004: Application-defined or object-defined error"
your advice is highly appreciated!!

 

[Noni]

@Peter_SSs Thank you so much!!
i've made the changes you recommended. now I'm getting error "Run time error 1004: Application-defined or object-defined error"
your advice is highly appreciated!!

@Peter_SSs i realized i had to change fix another "i". I've done that now.
now when I click on command button, I get below on Sheet2

Worksheets - Copy.xlsm
	A	B	C	D
1	2022 Clients	2022 Data	2022 Data2	2022 Data3
2	Peter	6543	23	0
3	Sally	9854	23	56
4	Michelle	1265	1256	64
5	Anna	7834	23	0
6	Ben	1234	56	34
7	Michael	1234	1234	2345
8	David	1276	1234	3
9	Anthony	1234	23	0
10	Sue	8765	34	78
11	2022 Clients	2022 Data	2022 Data2	2022 Data3
12	2022 Clients	2022 Data	2022 Data2	2022 Data3
13	2022 Clients	2022 Data	2022 Data2	2022 Data3
14	2022 Clients	2022 Data	2022 Data2	2022 Data3
15	2022 Clients	2022 Data	2022 Data2	2022 Data3
16	2022 Clients	2022 Data	2022 Data2	2022 Data3
17	2022 Clients	2022 Data	2022 Data2	2022 Data3
18	2022 Clients	2022 Data	2022 Data2	2022 Data3
19	2022 Clients	2022 Data	2022 Data2	2022 Data3
20	2022 Clients	2022 Data	2022 Data2	2022 Data3
21	2022 Clients	2022 Data	2022 Data2	2022 Data3
22	2022 Clients	2022 Data	2022 Data2	2022 Data3
23	2022 Clients	2022 Data	2022 Data2	2022 Data3
24	2022 Clients	2022 Data	2022 Data2	2022 Data3
25	2022 Clients	2022 Data	2022 Data2	2022 Data3
26	2022 Clients	2022 Data	2022 Data2	2022 Data3
27	2022 Clients	2022 Data	2022 Data2	2022 Data3
28	2022 Clients	2022 Data	2022 Data2	2022 Data3
29	2022 Clients	2022 Data	2022 Data2	2022 Data3
30	2022 Clients	2022 Data	2022 Data2	2022 Data3
31	Michael	1234	1234	2345
32	Michael	1234	1234	2345
33	Michael	1234	1234	2345
34	Michael	1234	1234	2345
35	Michael	1234	1234	2345
36	Michael	1234	1234	2345
37	Michael	1234	1234	2345
38	Michael	1234	1234	2345
39	Michael	1234	1234	2345
40	Michael	1234	1234	2345
41	Michael	1234	1234	2345
42	Michael	1234	1234	2345
43	Michael	1234	1234	2345
44	Michael	1234	1234	2345
45	Michael	1234	1234	2345
46	Michael	1234	1234	2345
47	Michael	1234	1234	2345
48	Michael	1234	1234	2345
49	Michael	1234	1234	2345
50	Sarah	4321	54	67
51	Sarah	4321	54	67
52	Sarah	4321	54	67
53	Sarah	4321	54	67
54	Sarah	4321	54	67
55	Sarah	4321	54	67
56	Sarah	4321	54	67
57	Sarah	4321	54	67
58	Sarah	4321	54	67
59	Sarah	4321	54	67
60	Sarah	4321	54	67
61	Sarah	4321	54	67
62	Sarah	4321	54	67
63	Sarah	4321	54	67
64	Sarah	4321	54	67
65	Sarah	4321	54	67
66	Sarah	4321	54	67
67	Sarah	4321	54	67
68	Sarah	4321	54	67
69	Sarah	4321	54	67
70	Mary	9876	0	34
71	Mary	9876	0	34
72	Mary	9876	0	34
73	Mary	9876	0	34
74	Mary	9876	0	34
75	Mary	9876	0	34
76	Mary	9876	0	34
77	Mary	9876	0	34
78	Mary	9876	0	34
79	Mary	9876	0	34
80	Mary	9876	0	34
81	Mary	9876	0	34
82	Mary	9876	0	34
83	Mary	9876	0	34
84	Mary	9876	0	34
85	Mary	9876	0	34
86	Mary	9876	0	34
87	Mary	9876	0	34
88	Mary	9876	0	34
89	Mary	9876	0	34
90	Rachel	5678	12	45
91	Rachel	5678	12	45
92	Rachel	5678	12	45
93	Rachel	5678	12	45
94	Rachel	5678	12	45
95	Rachel	5678	12	45
96	Rachel	5678	12	45
97	Rachel	5678	12	45
98	Rachel	5678	12	45
99	Rachel	5678	12	45
100	Rachel	5678	12	45
101	Rachel	5678	12	45
102	Rachel	5678	12	45
103	Rachel	5678	12	45
104	Rachel	5678	12	45
105	Rachel	5678	12	45
106	Rachel	5678	12	45
107	Rachel	5678	12	45
108	Rachel	5678	12	45
109	Rachel	5678	12	45
110	Anna	7834	23	0
111	Anna	7834	23	0
112	Anna	7834	23	0
113	Anna	7834	23	0
114	Anna	7834	23	0
115	Anna	7834	23	0
116	Anna	7834	23	0
117	Anna	7834	23	0
118	Anna	7834	23	0
119	Anna	7834	23	0
120	Anna	7834	23	0
121	Anna	7834	23	0
122	Anna	7834	23	0
123	Anna	7834	23	0
124	Anna	7834	23	0
125	Anna	7834	23	0
126	Anna	7834	23	0
127	Anna	7834	23	0
128	Anna	7834	23	0
129	Monica	2356	45	45
130	Monica	2356	45	45
131	Monica	2356	45	45
132	Monica	2356	45	45
133	Monica	2356	45	45
134	Monica	2356	45	45
135	Monica	2356	45	45
136	Monica	2356	45	45
137	Monica	2356	45	45
138	Monica	2356	45	45
139	Monica	2356	45	45
140	Monica	2356	45	45
141	Monica	2356	45	45
142	Monica	2356	45	45
143	Monica	2356	45	45
144	Monica	2356	45	45
145	Monica	2356	45	45
146	Monica	2356	45	45
147	Monica	2356	45	45
148	Monica	2356	45	45
149	Charles	1234	0	0
150	Charles	1234	0	0
151	Charles	1234	0	0
152	Charles	1234	0	0
153	Charles	1234	0	0
154	Charles	1234	0	0
155	Charles	1234	0	0
156	Charles	1234	0	0
157	Charles	1234	0	0
158	Charles	1234	0	0
159	Charles	1234	0	0
160	Charles	1234	0	0
161	Charles	1234	0	0
162	Charles	1234	0	0
163	Charles	1234	0	0
164	Charles	1234	0	0
165	Charles	1234	0	0
166	Charles	1234	0	0
167	Charles	1234	0	0
168	Charles	1234	0	0
169	Peter	6543	23	0
170	Peter	6543	23	0
171	Peter	6543	23	0
172	Peter	6543	23	0
173	Peter	6543	23	0
174	Peter	6543	23	0
175	Peter	6543	23	0
176	Peter	6543	23	0
177	Peter	6543	23	0
178	Peter	6543	23	0
179	Peter	6543	23	0
180	Peter	6543	23	0
181	Peter	6543	23	0
182	Peter	6543	23	0
183	Peter	6543	23	0
184	Peter	6543	23	0
185	Peter	6543	23	0
186	Peter	6543	23	0
187	Peter	6543	23	0
188	Anthony	1234	23	0
189	Anthony	1234	23	0
190	Anthony	1234	23	0
191	Anthony	1234	23	0
192	Anthony	1234	23	0
193	Anthony	1234	23	0
194	Anthony	1234	23	0
195	Anthony	1234	23	0
196	Anthony	1234	23	0
197	Anthony	1234	23	0
198	Anthony	1234	23	0
199	Anthony	1234	23	0
200	Anthony	1234	23	0
201	Anthony	1234	23	0
202	Anthony	1234	23	0
203	Anthony	1234	23	0
204	Anthony	1234	23	0
205	Anthony	1234	23	0
206	Anthony	1234	23	0
207	Ben	1234	56	34
208	Ben	1234	56	34
209	Ben	1234	56	34
210	Ben	1234	56	34
211	Ben	1234	56	34
212	Ben	1234	56	34
213	Ben	1234	56	34
214	Ben	1234	56	34
215	Ben	1234	56	34
216	Ben	1234	56	34
217	Ben	1234	56	34
218	Ben	1234	56	34
219	Ben	1234	56	34
220	Ben	1234	56	34
221	Ben	1234	56	34
222	Ben	1234	56	34
223	Ben	1234	56	34
224	Ben	1234	56	34
225	Ben	1234	56	34
226	Elizabeth	6543	0	78
227	Elizabeth	6543	0	78
228	Elizabeth	6543	0	78
229	Elizabeth	6543	0	78
230	Elizabeth	6543	0	78
231	Elizabeth	6543	0	78
232	Elizabeth	6543	0	78
233	Elizabeth	6543	0	78
234	Elizabeth	6543	0	78
235	Elizabeth	6543	0	78
236	Elizabeth	6543	0	78
237	Elizabeth	6543	0	78
238	Elizabeth	6543	0	78
239	Elizabeth	6543	0	78
240	Elizabeth	6543	0	78
241	Elizabeth	6543	0	78
242	Elizabeth	6543	0	78
243	Elizabeth	6543	0	78
244	Elizabeth	6543	0	78
245	Elizabeth	6543	0	78
246	Wong	1276	23456	12
247	Wong	1276	23456	12
248	Wong	1276	23456	12
249	Wong	1276	23456	12
250	Wong	1276	23456	12
251	Wong	1276	23456	12
252	Wong	1276	23456	12
253	Wong	1276	23456	12
254	Wong	1276	23456	12
255	Wong	1276	23456	12
256	Wong	1276	23456	12
257	Wong	1276	23456	12
258	Wong	1276	23456	12
259	Wong	1276	23456	12
260	Wong	1276	23456	12
261	Wong	1276	23456	12
262	Wong	1276	23456	12
263	Wong	1276	23456	12
264	Wong	1276	23456	12
265	Wong	1276	23456	12
266	Sally	9854	23	56
267	Sally	9854	23	56
268	Sally	9854	23	56
269	Sally	9854	23	56
270	Sally	9854	23	56
271	Sally	9854	23	56
272	Sally	9854	23	56
273	Sally	9854	23	56
274	Sally	9854	23	56
275	Sally	9854	23	56
276	Sally	9854	23	56
277	Sally	9854	23	56
278	Sally	9854	23	56
279	Sally	9854	23	56
280	Sally	9854	23	56
281	Sally	9854	23	56
282	Sally	9854	23	56
283	Sally	9854	23	56
284	Sally	9854	23	56
285	Jay	4325	8765	89
286	Jay	4325	8765	89
287	Jay	4325	8765	89
288	Jay	4325	8765	89
289	Jay	4325	8765	89
290	Jay	4325	8765	89
291	Jay	4325	8765	89
292	Jay	4325	8765	89
293	Jay	4325	8765	89
294	Jay	4325	8765	89
295	Jay	4325	8765	89
296	Jay	4325	8765	89
297	Jay	4325	8765	89
298	Jay	4325	8765	89
299	Jay	4325	8765	89
300	Jay	4325	8765	89
301	Jay	4325	8765	89
302	Jay	4325	8765	89
303	Jay	4325	8765	89
304	Jay	4325	8765	89
305	Michelle	1265	1256	64
306	Michelle	1265	1256	64
307	Michelle	1265	1256	64
308	Michelle	1265	1256	64
309	Michelle	1265	1256	64
310	Michelle	1265	1256	64
311	Michelle	1265	1256	64
312	Michelle	1265	1256	64
313	Michelle	1265	1256	64
314	Michelle	1265	1256	64
315	Michelle	1265	1256	64
316	Michelle	1265	1256	64
317	Michelle	1265	1256	64
318	Michelle	1265	1256	64
319	Michelle	1265	1256	64
320	Michelle	1265	1256	64
321	Michelle	1265	1256	64
322	Michelle	1265	1256	64
323	Michelle	1265	1256	64
324	David	1276	1234	3
325	David	1276	1234	3
326	David	1276	1234	3
327	David	1276	1234	3
328	David	1276	1234	3
329	David	1276	1234	3
330	David	1276	1234	3
331	David	1276	1234	3
332	David	1276	1234	3
333	David	1276	1234	3
334	David	1276	1234	3
335	David	1276	1234	3
336	David	1276	1234	3
337	David	1276	1234	3
338	David	1276	1234	3
339	David	1276	1234	3
340	David	1276	1234	3
341	David	1276	1234	3
342	David	1276	1234	3
343	Jennifer	1234	8765	56
344	Jennifer	1234	8765	56
345	Jennifer	1234	8765	56
346	Jennifer	1234	8765	56
347	Jennifer	1234	8765	56
348	Jennifer	1234	8765	56
349	Jennifer	1234	8765	56
350	Jennifer	1234	8765	56
351	Jennifer	1234	8765	56
352	Jennifer	1234	8765	56
353	Jennifer	1234	8765	56
354	Jennifer	1234	8765	56
355	Jennifer	1234	8765	56
356	Jennifer	1234	8765	56
357	Jennifer	1234	8765	56
358	Jennifer	1234	8765	56
359	Jennifer	1234	8765	56
360	Jennifer	1234	8765	56
361	Jennifer	1234	8765	56
362	Jennifer	1234	8765	56
363	Sue	8765	34	78
364	Sue	8765	34	78
365	Sue	8765	34	78
366	Sue	8765	34	78
367	Sue	8765	34	78
368	Sue	8765	34	78
369	Sue	8765	34	78
370	Sue	8765	34	78
371	Sue	8765	34	78
372	Sue	8765	34	78
373	Sue	8765	34	78
374	Sue	8765	34	78
375	Sue	8765	34	78
376	Sue	8765	34	78
377	Sue	8765	34	78
378	Sue	8765	34	78
379	Sue	8765	34	78
380	Sue	8765	34	78
381	Sue	8765	34	78
382	Ruba	3254	23	9
383	Ruba	3254	23	9
384	Ruba	3254	23	9
385	Ruba	3254	23	9
386	Ruba	3254	23	9
387	Ruba	3254	23	9
388	Ruba	3254	23	9
389	Ruba	3254	23	9
390	Ruba	3254	23	9
391	Ruba	3254	23	9
392	Ruba	3254	23	9
393	Ruba	3254	23	9
394	Ruba	3254	23	9
395	Ruba	3254	23	9
396	Ruba	3254	23	9
397	Ruba	3254	23	9
398	Ruba	3254	23	9
399	Ruba	3254	23	9
400	Ruba	3254	23	9
401	Ruba	3254	23	9
402	Henry	9876	78	0
403	Henry	9876	78	0
404	Henry	9876	78	0
405	Henry	9876	78	0
406	Henry	9876	78	0
407	Henry	9876	78	0
408	Henry	9876	78	0
409	Henry	9876	78	0
410	Henry	9876	78	0
411	Henry	9876	78	0
412	Henry	9876	78	0
413	Henry	9876	78	0
414	Henry	9876	78	0
415	Henry	9876	78	0
416	Henry	9876	78	0
417	Henry	9876	78	0
418	Henry	9876	78	0
419	Henry	9876	78	0
420	Henry	9876	78	0
421	Henry	9876	78	0
422	Chloe	3245	34567	6
423	Chloe	3245	34567	6
424	Chloe	3245	34567	6
425	Chloe	3245	34567	6
426	Chloe	3245	34567	6
427	Chloe	3245	34567	6
428	Chloe	3245	34567	6
429	Chloe	3245	34567	6
430	Chloe	3245	34567	6
431	Chloe	3245	34567	6
432	Chloe	3245	34567	6
433	Chloe	3245	34567	6
434	Chloe	3245	34567	6
435	Chloe	3245	34567	6
436	Chloe	3245	34567	6
437	Chloe	3245	34567	6
438	Chloe	3245	34567	6
439	Chloe	3245	34567	6
440	Chloe	3245	34567	6
441	Chloe	3245	34567	6
442	Candy	2389	356	5
443	Candy	2389	356	5
444	Candy	2389	356	5
445	Candy	2389	356	5
446	Candy	2389	356	5
447	Candy	2389	356	5
448	Candy	2389	356	5
449	Candy	2389	356	5
450	Candy	2389	356	5
451	Candy	2389	356	5
452	Candy	2389	356	5
453	Candy	2389	356	5
454	Candy	2389	356	5
455	Candy	2389	356	5
456	Candy	2389	356	5
457	Candy	2389	356	5
458	Candy	2389	356	5
459	Candy	2389	356	5
460	Candy	2389	356	5
461	Candy	2389	356	5
Sheet2

code is s

Private Sub CommandButton2_Click()
    c = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    cc = Worksheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row

  For j = 1 To c
    For jj = 1 To cc
      
            If Worksheets("Sheet1").Cells(j, 1).Value <> Worksheets("Sheet1").Cells(jj, 5).Value Then

                Worksheets("Sheet1").Range("a" & j & ":d" & j).Copy

                Worksheets("Sheet2").Activate


                b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

                Worksheets("Sheet2").Cells(b + 1, 1).Select

                ActiveSheet.Paste

                Worksheets("Sheet1").Activate

            End If


        Next: Next

    Application.CutCopyMode = False

    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select


End Sub

 

[Noni]

@Peter_SSs i realized i had to change fix another "i". I've done that now.
now when I click on command button, I get below on Sheet2

Worksheets - Copy.xlsm
	A	B	C	D
1	2022 Clients	2022 Data	2022 Data2	2022 Data3
2	Peter	6543	23	0
3	Sally	9854	23	56
4	Michelle	1265	1256	64
5	Anna	7834	23	0
6	Ben	1234	56	34
7	Michael	1234	1234	2345
8	David	1276	1234	3
9	Anthony	1234	23	0
10	Sue	8765	34	78
11	2022 Clients	2022 Data	2022 Data2	2022 Data3
12	2022 Clients	2022 Data	2022 Data2	2022 Data3
13	2022 Clients	2022 Data	2022 Data2	2022 Data3
14	2022 Clients	2022 Data	2022 Data2	2022 Data3
15	2022 Clients	2022 Data	2022 Data2	2022 Data3
16	2022 Clients	2022 Data	2022 Data2	2022 Data3
17	2022 Clients	2022 Data	2022 Data2	2022 Data3
18	2022 Clients	2022 Data	2022 Data2	2022 Data3
19	2022 Clients	2022 Data	2022 Data2	2022 Data3
20	2022 Clients	2022 Data	2022 Data2	2022 Data3
21	2022 Clients	2022 Data	2022 Data2	2022 Data3
22	2022 Clients	2022 Data	2022 Data2	2022 Data3
23	2022 Clients	2022 Data	2022 Data2	2022 Data3
24	2022 Clients	2022 Data	2022 Data2	2022 Data3
25	2022 Clients	2022 Data	2022 Data2	2022 Data3
26	2022 Clients	2022 Data	2022 Data2	2022 Data3
27	2022 Clients	2022 Data	2022 Data2	2022 Data3
28	2022 Clients	2022 Data	2022 Data2	2022 Data3
29	2022 Clients	2022 Data	2022 Data2	2022 Data3
30	2022 Clients	2022 Data	2022 Data2	2022 Data3
31	Michael	1234	1234	2345
32	Michael	1234	1234	2345
33	Michael	1234	1234	2345
34	Michael	1234	1234	2345
35	Michael	1234	1234	2345
36	Michael	1234	1234	2345
37	Michael	1234	1234	2345
38	Michael	1234	1234	2345
39	Michael	1234	1234	2345
40	Michael	1234	1234	2345
41	Michael	1234	1234	2345
42	Michael	1234	1234	2345
43	Michael	1234	1234	2345
44	Michael	1234	1234	2345
45	Michael	1234	1234	2345
46	Michael	1234	1234	2345
47	Michael	1234	1234	2345
48	Michael	1234	1234	2345
49	Michael	1234	1234	2345
50	Sarah	4321	54	67
51	Sarah	4321	54	67
52	Sarah	4321	54	67
53	Sarah	4321	54	67
54	Sarah	4321	54	67
55	Sarah	4321	54	67
56	Sarah	4321	54	67
57	Sarah	4321	54	67
58	Sarah	4321	54	67
59	Sarah	4321	54	67
60	Sarah	4321	54	67
61	Sarah	4321	54	67
62	Sarah	4321	54	67
63	Sarah	4321	54	67
64	Sarah	4321	54	67
65	Sarah	4321	54	67
66	Sarah	4321	54	67
67	Sarah	4321	54	67
68	Sarah	4321	54	67
69	Sarah	4321	54	67
70	Mary	9876	0	34
71	Mary	9876	0	34
72	Mary	9876	0	34
73	Mary	9876	0	34
74	Mary	9876	0	34
75	Mary	9876	0	34
76	Mary	9876	0	34
77	Mary	9876	0	34
78	Mary	9876	0	34
79	Mary	9876	0	34
80	Mary	9876	0	34
81	Mary	9876	0	34
82	Mary	9876	0	34
83	Mary	9876	0	34
84	Mary	9876	0	34
85	Mary	9876	0	34
86	Mary	9876	0	34
87	Mary	9876	0	34
88	Mary	9876	0	34
89	Mary	9876	0	34
90	Rachel	5678	12	45
91	Rachel	5678	12	45
92	Rachel	5678	12	45
93	Rachel	5678	12	45
94	Rachel	5678	12	45
95	Rachel	5678	12	45
96	Rachel	5678	12	45
97	Rachel	5678	12	45
98	Rachel	5678	12	45
99	Rachel	5678	12	45
100	Rachel	5678	12	45
101	Rachel	5678	12	45
102	Rachel	5678	12	45
103	Rachel	5678	12	45
104	Rachel	5678	12	45
105	Rachel	5678	12	45
106	Rachel	5678	12	45
107	Rachel	5678	12	45
108	Rachel	5678	12	45
109	Rachel	5678	12	45
110	Anna	7834	23	0
111	Anna	7834	23	0
112	Anna	7834	23	0
113	Anna	7834	23	0
114	Anna	7834	23	0
115	Anna	7834	23	0
116	Anna	7834	23	0
117	Anna	7834	23	0
118	Anna	7834	23	0
119	Anna	7834	23	0
120	Anna	7834	23	0
121	Anna	7834	23	0
122	Anna	7834	23	0
123	Anna	7834	23	0
124	Anna	7834	23	0
125	Anna	7834	23	0
126	Anna	7834	23	0
127	Anna	7834	23	0
128	Anna	7834	23	0
129	Monica	2356	45	45
130	Monica	2356	45	45
131	Monica	2356	45	45
132	Monica	2356	45	45
133	Monica	2356	45	45
134	Monica	2356	45	45
135	Monica	2356	45	45
136	Monica	2356	45	45
137	Monica	2356	45	45
138	Monica	2356	45	45
139	Monica	2356	45	45
140	Monica	2356	45	45
141	Monica	2356	45	45
142	Monica	2356	45	45
143	Monica	2356	45	45
144	Monica	2356	45	45
145	Monica	2356	45	45
146	Monica	2356	45	45
147	Monica	2356	45	45
148	Monica	2356	45	45
149	Charles	1234	0	0
150	Charles	1234	0	0
151	Charles	1234	0	0
152	Charles	1234	0	0
153	Charles	1234	0	0
154	Charles	1234	0	0
155	Charles	1234	0	0
156	Charles	1234	0	0
157	Charles	1234	0	0
158	Charles	1234	0	0
159	Charles	1234	0	0
160	Charles	1234	0	0
161	Charles	1234	0	0
162	Charles	1234	0	0
163	Charles	1234	0	0
164	Charles	1234	0	0
165	Charles	1234	0	0
166	Charles	1234	0	0
167	Charles	1234	0	0
168	Charles	1234	0	0
169	Peter	6543	23	0
170	Peter	6543	23	0
171	Peter	6543	23	0
172	Peter	6543	23	0
173	Peter	6543	23	0
174	Peter	6543	23	0
175	Peter	6543	23	0
176	Peter	6543	23	0
177	Peter	6543	23	0
178	Peter	6543	23	0
179	Peter	6543	23	0
180	Peter	6543	23	0
181	Peter	6543	23	0
182	Peter	6543	23	0
183	Peter	6543	23	0
184	Peter	6543	23	0
185	Peter	6543	23	0
186	Peter	6543	23	0
187	Peter	6543	23	0
188	Anthony	1234	23	0
189	Anthony	1234	23	0
190	Anthony	1234	23	0
191	Anthony	1234	23	0
192	Anthony	1234	23	0
193	Anthony	1234	23	0
194	Anthony	1234	23	0
195	Anthony	1234	23	0
196	Anthony	1234	23	0
197	Anthony	1234	23	0
198	Anthony	1234	23	0
199	Anthony	1234	23	0
200	Anthony	1234	23	0
201	Anthony	1234	23	0
202	Anthony	1234	23	0
203	Anthony	1234	23	0
204	Anthony	1234	23	0
205	Anthony	1234	23	0
206	Anthony	1234	23	0
207	Ben	1234	56	34
208	Ben	1234	56	34
209	Ben	1234	56	34
210	Ben	1234	56	34
211	Ben	1234	56	34
212	Ben	1234	56	34
213	Ben	1234	56	34
214	Ben	1234	56	34
215	Ben	1234	56	34
216	Ben	1234	56	34
217	Ben	1234	56	34
218	Ben	1234	56	34
219	Ben	1234	56	34
220	Ben	1234	56	34
221	Ben	1234	56	34
222	Ben	1234	56	34
223	Ben	1234	56	34
224	Ben	1234	56	34
225	Ben	1234	56	34
226	Elizabeth	6543	0	78
227	Elizabeth	6543	0	78
228	Elizabeth	6543	0	78
229	Elizabeth	6543	0	78
230	Elizabeth	6543	0	78
231	Elizabeth	6543	0	78
232	Elizabeth	6543	0	78
233	Elizabeth	6543	0	78
234	Elizabeth	6543	0	78
235	Elizabeth	6543	0	78
236	Elizabeth	6543	0	78
237	Elizabeth	6543	0	78
238	Elizabeth	6543	0	78
239	Elizabeth	6543	0	78
240	Elizabeth	6543	0	78
241	Elizabeth	6543	0	78
242	Elizabeth	6543	0	78
243	Elizabeth	6543	0	78
244	Elizabeth	6543	0	78
245	Elizabeth	6543	0	78
246	Wong	1276	23456	12
247	Wong	1276	23456	12
248	Wong	1276	23456	12
249	Wong	1276	23456	12
250	Wong	1276	23456	12
251	Wong	1276	23456	12
252	Wong	1276	23456	12
253	Wong	1276	23456	12
254	Wong	1276	23456	12
255	Wong	1276	23456	12
256	Wong	1276	23456	12
257	Wong	1276	23456	12
258	Wong	1276	23456	12
259	Wong	1276	23456	12
260	Wong	1276	23456	12
261	Wong	1276	23456	12
262	Wong	1276	23456	12
263	Wong	1276	23456	12
264	Wong	1276	23456	12
265	Wong	1276	23456	12
266	Sally	9854	23	56
267	Sally	9854	23	56
268	Sally	9854	23	56
269	Sally	9854	23	56
270	Sally	9854	23	56
271	Sally	9854	23	56
272	Sally	9854	23	56
273	Sally	9854	23	56
274	Sally	9854	23	56
275	Sally	9854	23	56
276	Sally	9854	23	56
277	Sally	9854	23	56
278	Sally	9854	23	56
279	Sally	9854	23	56
280	Sally	9854	23	56
281	Sally	9854	23	56
282	Sally	9854	23	56
283	Sally	9854	23	56
284	Sally	9854	23	56
285	Jay	4325	8765	89
286	Jay	4325	8765	89
287	Jay	4325	8765	89
288	Jay	4325	8765	89
289	Jay	4325	8765	89
290	Jay	4325	8765	89
291	Jay	4325	8765	89
292	Jay	4325	8765	89
293	Jay	4325	8765	89
294	Jay	4325	8765	89
295	Jay	4325	8765	89
296	Jay	4325	8765	89
297	Jay	4325	8765	89
298	Jay	4325	8765	89
299	Jay	4325	8765	89
300	Jay	4325	8765	89
301	Jay	4325	8765	89
302	Jay	4325	8765	89
303	Jay	4325	8765	89
304	Jay	4325	8765	89
305	Michelle	1265	1256	64
306	Michelle	1265	1256	64
307	Michelle	1265	1256	64
308	Michelle	1265	1256	64
309	Michelle	1265	1256	64
310	Michelle	1265	1256	64
311	Michelle	1265	1256	64
312	Michelle	1265	1256	64
313	Michelle	1265	1256	64
314	Michelle	1265	1256	64
315	Michelle	1265	1256	64
316	Michelle	1265	1256	64
317	Michelle	1265	1256	64
318	Michelle	1265	1256	64
319	Michelle	1265	1256	64
320	Michelle	1265	1256	64
321	Michelle	1265	1256	64
322	Michelle	1265	1256	64
323	Michelle	1265	1256	64
324	David	1276	1234	3
325	David	1276	1234	3
326	David	1276	1234	3
327	David	1276	1234	3
328	David	1276	1234	3
329	David	1276	1234	3
330	David	1276	1234	3
331	David	1276	1234	3
332	David	1276	1234	3
333	David	1276	1234	3
334	David	1276	1234	3
335	David	1276	1234	3
336	David	1276	1234	3
337	David	1276	1234	3
338	David	1276	1234	3
339	David	1276	1234	3
340	David	1276	1234	3
341	David	1276	1234	3
342	David	1276	1234	3
343	Jennifer	1234	8765	56
344	Jennifer	1234	8765	56
345	Jennifer	1234	8765	56
346	Jennifer	1234	8765	56
347	Jennifer	1234	8765	56
348	Jennifer	1234	8765	56
349	Jennifer	1234	8765	56
350	Jennifer	1234	8765	56
351	Jennifer	1234	8765	56
352	Jennifer	1234	8765	56
353	Jennifer	1234	8765	56
354	Jennifer	1234	8765	56
355	Jennifer	1234	8765	56
356	Jennifer	1234	8765	56
357	Jennifer	1234	8765	56
358	Jennifer	1234	8765	56
359	Jennifer	1234	8765	56
360	Jennifer	1234	8765	56
361	Jennifer	1234	8765	56
362	Jennifer	1234	8765	56
363	Sue	8765	34	78
364	Sue	8765	34	78
365	Sue	8765	34	78
366	Sue	8765	34	78
367	Sue	8765	34	78
368	Sue	8765	34	78
369	Sue	8765	34	78
370	Sue	8765	34	78
371	Sue	8765	34	78
372	Sue	8765	34	78
373	Sue	8765	34	78
374	Sue	8765	34	78
375	Sue	8765	34	78
376	Sue	8765	34	78
377	Sue	8765	34	78
378	Sue	8765	34	78
379	Sue	8765	34	78
380	Sue	8765	34	78
381	Sue	8765	34	78
382	Ruba	3254	23	9
383	Ruba	3254	23	9
384	Ruba	3254	23	9
385	Ruba	3254	23	9
386	Ruba	3254	23	9
387	Ruba	3254	23	9
388	Ruba	3254	23	9
389	Ruba	3254	23	9
390	Ruba	3254	23	9
391	Ruba	3254	23	9
392	Ruba	3254	23	9
393	Ruba	3254	23	9
394	Ruba	3254	23	9
395	Ruba	3254	23	9
396	Ruba	3254	23	9
397	Ruba	3254	23	9
398	Ruba	3254	23	9
399	Ruba	3254	23	9
400	Ruba	3254	23	9
401	Ruba	3254	23	9
402	Henry	9876	78	0
403	Henry	9876	78	0
404	Henry	9876	78	0
405	Henry	9876	78	0
406	Henry	9876	78	0
407	Henry	9876	78	0
408	Henry	9876	78	0
409	Henry	9876	78	0
410	Henry	9876	78	0
411	Henry	9876	78	0
412	Henry	9876	78	0
413	Henry	9876	78	0
414	Henry	9876	78	0
415	Henry	9876	78	0
416	Henry	9876	78	0
417	Henry	9876	78	0
418	Henry	9876	78	0
419	Henry	9876	78	0
420	Henry	9876	78	0
421	Henry	9876	78	0
422	Chloe	3245	34567	6
423	Chloe	3245	34567	6
424	Chloe	3245	34567	6
425	Chloe	3245	34567	6
426	Chloe	3245	34567	6
427	Chloe	3245	34567	6
428	Chloe	3245	34567	6
429	Chloe	3245	34567	6
430	Chloe	3245	34567	6
431	Chloe	3245	34567	6
432	Chloe	3245	34567	6
433	Chloe	3245	34567	6
434	Chloe	3245	34567	6
435	Chloe	3245	34567	6
436	Chloe	3245	34567	6
437	Chloe	3245	34567	6
438	Chloe	3245	34567	6
439	Chloe	3245	34567	6
440	Chloe	3245	34567	6
441	Chloe	3245	34567	6
442	Candy	2389	356	5
443	Candy	2389	356	5
444	Candy	2389	356	5
445	Candy	2389	356	5
446	Candy	2389	356	5
447	Candy	2389	356	5
448	Candy	2389	356	5
449	Candy	2389	356	5
450	Candy	2389	356	5
451	Candy	2389	356	5
452	Candy	2389	356	5
453	Candy	2389	356	5
454	Candy	2389	356	5
455	Candy	2389	356	5
456	Candy	2389	356	5
457	Candy	2389	356	5
458	Candy	2389	356	5
459	Candy	2389	356	5
460	Candy	2389	356	5
461	Candy	2389	356	5
Sheet2

code is s

Private Sub CommandButton2_Click()
    c = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    cc = Worksheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row

  For j = 1 To c
    For jj = 1 To cc
     
            If Worksheets("Sheet1").Cells(j, 1).Value <> Worksheets("Sheet1").Cells(jj, 5).Value Then

                Worksheets("Sheet1").Range("a" & j & ":d" & j).Copy

                Worksheets("Sheet2").Activate


                b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

                Worksheets("Sheet2").Cells(b + 1, 1).Select

                ActiveSheet.Paste

                Worksheets("Sheet1").Activate

            End If


        Next: Next

    Application.CutCopyMode = False

    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select


End Sub

I've 2 command buttons. first one: copies existing clients data to Sheet2 and second button is supposed to copy new client's data to Sheet2.

Private Sub CommandButton1_Click()

    a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    aa = Worksheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row
  For ii = 1 To aa
    For i = 1 To a
      
            If Worksheets("Sheet1").Cells(ii, 5).Value = Worksheets("Sheet1").Cells(i, 1).Value Then

                Worksheets("Sheet1").Range("a" & i & ":d" & i).Copy

                Worksheets("Sheet2").Activate


                b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

                Worksheets("Sheet2").Cells(b + 1, 1).Select

                ActiveSheet.Paste

                Worksheets("Sheet1").Activate

            End If


        Next: Next

    Application.CutCopyMode = False

    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

   

End Sub

Private Sub CommandButton2_Click()
    c = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    cc = Worksheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row

  For j = 1 To c
    For jj = 1 To cc
      
            If Worksheets("Sheet1").Cells(j, 1).Value <> Worksheets("Sheet1").Cells(jj, 5).Value Then

                Worksheets("Sheet1").Range("a" & j & ":d" & j).Copy

                Worksheets("Sheet2").Activate


                b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

                Worksheets("Sheet2").Cells(b + 1, 1).Select

                ActiveSheet.Paste

                Worksheets("Sheet1").Activate

            End If


        Next: Next

    Application.CutCopyMode = False

    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select


End Sub

desired outcome is below

Worksheets - Copy.xlsm
	A	B	C	D
1	2022 Clients	2022 Data	2022 Data2	2022 Data3
2	Peter	6543	23	0
3	Sally	9854	23	56
4	Michelle	1265	1256	64
5	Anna	7834	23	0
6	Ben	1234	56	34
7	Michael	1234	1234	2345
8	David	1276	1234	3
9	Anthony	1234	23	0
10	Sue	8765	34	78
11	Sarah	4321	54	67
12	Mary	9876	0	34
13	Rachel	5678	12	45
14	Monica	2356	45	45
15	Charles	1234	0	0
16	Elizabeth	6543	0	78
17	Wong	1276	23456	12
18	Jay	4325	8765	89
19	Jennifer	1234	8765	56
20	Ruba	3254	23	9
21	Henry	9876	78	0
22	Chloe	3245	34567	6
23	Candy	2389	356	5
Sheet2

button 1 does the job but when I press button 2messes up everythin. it copies same row multiple times..

 

[Noni]

I've 2 command buttons. first one: copies existing clients data to Sheet2 and second button is supposed to copy new client's data to Sheet2.

Private Sub CommandButton1_Click()

    a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    aa = Worksheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row
  For ii = 1 To aa
    For i = 1 To a
     
            If Worksheets("Sheet1").Cells(ii, 5).Value = Worksheets("Sheet1").Cells(i, 1).Value Then

                Worksheets("Sheet1").Range("a" & i & ":d" & i).Copy

                Worksheets("Sheet2").Activate


                b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

                Worksheets("Sheet2").Cells(b + 1, 1).Select

                ActiveSheet.Paste

                Worksheets("Sheet1").Activate

            End If


        Next: Next

    Application.CutCopyMode = False

    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

  

End Sub

Private Sub CommandButton2_Click()
    c = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    cc = Worksheets("Sheet1").Cells(Rows.Count, 5).End(xlUp).Row

  For j = 1 To c
    For jj = 1 To cc
     
            If Worksheets("Sheet1").Cells(j, 1).Value <> Worksheets("Sheet1").Cells(jj, 5).Value Then

                Worksheets("Sheet1").Range("a" & j & ":d" & j).Copy

                Worksheets("Sheet2").Activate


                b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

                Worksheets("Sheet2").Cells(b + 1, 1).Select

                ActiveSheet.Paste

                Worksheets("Sheet1").Activate

            End If


        Next: Next

    Application.CutCopyMode = False

    ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select


End Sub

desired outcome is below

Worksheets - Copy.xlsm
	A	B	C	D
1	2022 Clients	2022 Data	2022 Data2	2022 Data3
2	Peter	6543	23	0
3	Sally	9854	23	56
4	Michelle	1265	1256	64
5	Anna	7834	23	0
6	Ben	1234	56	34
7	Michael	1234	1234	2345
8	David	1276	1234	3
9	Anthony	1234	23	0
10	Sue	8765	34	78
11	Sarah	4321	54	67
12	Mary	9876	0	34
13	Rachel	5678	12	45
14	Monica	2356	45	45
15	Charles	1234	0	0
16	Elizabeth	6543	0	78
17	Wong	1276	23456	12
18	Jay	4325	8765	89
19	Jennifer	1234	8765	56
20	Ruba	3254	23	9
21	Henry	9876	78	0
22	Chloe	3245	34567	6
23	Candy	2389	356	5
Sheet2

button 1 does the job but when I press button 2messes up everythin. it copies same row multiple times..

@Peter_SSs @mohadin could you please assist with the coding of command button2. much appreciated!!

 

[jasonb75]

I've had a look over your code and done a re-write using a more logical method. It looks a bit longer because I've tried to use variable names that describe what they are doing and added a few comments so that you can see what is happening.

This code will work with both buttons, you don't need separate procedures when the task is so similar for both. If your buttons are not named "Button 1" and "Button 2" then you will need to change those names in the code so that they match correctly.

After pasting the code into your module, to assign the code to both buttons, right click on the first button, then choose 'Assign Macro' from the menu and choose "CopyClientList" from the list of available procedures. Repeat these steps for the second button.

Having said that, if you always run both together then I can easily edit the code so that it completes both lists from one button (if desired).

Note that there appears to be an anomaly when running the code on the sample provided. I am looking into this and will post a correction later when I find the cause. For some reason, "Peter" is not being found on the 2021 client list and is being added with the new clients instead of the existing.

Option Explicit
Sub CopyClientList()

' Declare variables

Dim ws1 As Worksheet, ws2 As Worksheet
Dim ClientFound As Range, ClientList As Range, NextClient As Range, OldClients As Range
Dim EndOfList As Long, PasteRow As Long, EndOfOld As Long
Dim WhichButton As String

' Set worksheet objects

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

' find last row for each range to be used

EndOfList = ws1.Cells(Rows.Count, 1).End(xlUp).Row
PasteRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
EndOfOld = ws1.Cells(Rows.Count, 5).End(xlUp).Row

' set tables that need to be searched as range objects

Set ClientList = ws1.Range("A2:A" & EndOfList)
Set OldClients = ws1.Range("E5:E" & EndOfOld)

' check which button was clicked

WhichButton = Application.Caller

' loop through the names in the 2022 list

For Each NextClient In ClientList

    ' check if client is found in the 2021 list
    
    Set ClientFound = OldClients.Find(NextClient, , xlValues, xlWhole, xlByRows, , False, False, False)
    
    ' check if the client should be copied to sheet 2 or skipped based on which button was clicked
    ' if it is copied then increae the PasteRow ready for the next cycle, otherwise nothing happens
    ' note that the 2 negative commands in the line "Not ClientFound Is Nothing" cancel each other out, this is used to confirm that the record exists
    
    ' **** Change the names of "Button 1" and "Button 2" below to match the names of your actual buttons if different ****
    ' **** Note that the names are case sensitive, "Button" and "button" are not the same ****
    
    If WhichButton = "Button 1" Then
        If Not ClientFound Is Nothing Then
            NextClient.Resize(, 4).Copy ws2.Cells(PasteRow, 1)
            PasteRow = PasteRow + 1
        End If
    ElseIf WhichButton = "Button 2" Then
        If ClientFound Is Nothing Then
            NextClient.Resize(, 4).Copy ws2.Cells(PasteRow, 1)
            PasteRow = PasteRow + 1
        End If
    End If
Next NextClient

End Sub

 

[Noni]

I've had a look over your code and done a re-write using a more logical method. It looks a bit longer because I've tried to use variable names that describe what they are doing and added a few comments so that you can see what is happening.

This code will work with both buttons, you don't need separate procedures when the task is so similar for both. If your buttons are not named "Button 1" and "Button 2" then you will need to change those names in the code so that they match correctly.

After pasting the code into your module, to assign the code to both buttons, right click on the first button, then choose 'Assign Macro' from the menu and choose "CopyClientList" from the list of available procedures. Repeat these steps for the second button.

Having said that, if you always run both together then I can easily edit the code so that it completes both lists from one button (if desired).

Note that there appears to be an anomaly when running the code on the sample provided. I am looking into this and will post a correction later when I find the cause. For some reason, "Peter" is not being found on the 2021 client list and is being added with the new clients instead of the existing.

Option Explicit
Sub CopyClientList()

' Declare variables

Dim ws1 As Worksheet, ws2 As Worksheet
Dim ClientFound As Range, ClientList As Range, NextClient As Range, OldClients As Range
Dim EndOfList As Long, PasteRow As Long, EndOfOld As Long
Dim WhichButton As String

' Set worksheet objects

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

' find last row for each range to be used

EndOfList = ws1.Cells(Rows.Count, 1).End(xlUp).Row
PasteRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
EndOfOld = ws1.Cells(Rows.Count, 5).End(xlUp).Row

' set tables that need to be searched as range objects

Set ClientList = ws1.Range("A2:A" & EndOfList)
Set OldClients = ws1.Range("E5:E" & EndOfOld)

' check which button was clicked

WhichButton = Application.Caller

' loop through the names in the 2022 list

For Each NextClient In ClientList

    ' check if client is found in the 2021 list
   
    Set ClientFound = OldClients.Find(NextClient, , xlValues, xlWhole, xlByRows, , False, False, False)
   
    ' check if the client should be copied to sheet 2 or skipped based on which button was clicked
    ' if it is copied then increae the PasteRow ready for the next cycle, otherwise nothing happens
    ' note that the 2 negative commands in the line "Not ClientFound Is Nothing" cancel each other out, this is used to confirm that the record exists
   
    ' **** Change the names of "Button 1" and "Button 2" below to match the names of your actual buttons if different ****
    ' **** Note that the names are case sensitive, "Button" and "button" are not the same ****
   
    If WhichButton = "Button 1" Then
        If Not ClientFound Is Nothing Then
            NextClient.Resize(, 4).Copy ws2.Cells(PasteRow, 1)
            PasteRow = PasteRow + 1
        End If
    ElseIf WhichButton = "Button 2" Then
        If ClientFound Is Nothing Then
            NextClient.Resize(, 4).Copy ws2.Cells(PasteRow, 1)
            PasteRow = PasteRow + 1
        End If
    End If
Next NextClient

End Sub

Thank you so much! yes one button would be more convenient. By pressing this button, first old clients(2021 and 2022) should get copied in Sheet2 then new clients(2022) gets copied . this order is very important.

Book1
	A	B	C	D	E
1	2022 Clients	2022 Data	2022 Data2	2022 Data3	2021 Clients
2	Michael	1234	1234	2345	James
3	Sarah	4321	54	67	Sam
4	Mary	9876	0	34	Peter
5	Rachel	5678	12	45	Shaw
6	Anna	7834	23	0	Sally
7	Monica	2356	45	45	Michelle
8	Charles	1234	0	0	Ivona
9	Peter	6543	23	0	Anna
10	Anthony	1234	23	0	Claire
11	Ben	1234	56	34	Ben
12	Elizabeth	6543	0	78	Michael
13	Wong	1276	23456	12	David
14	Sally	9854	23	56	Annaleise
15	Jay	4325	8765	89	Chris
16	Michelle	1265	1256	64	John
17	David	1276	1234	3	Bob
18	Jennifer	1234	8765	56	Anthony
19	Sue	8765	34	78	Sue
20	Ruba	3254	23	9	Robin
21	Henry	9876	78	0
22	Chloe	3245	34567	6
23	Candy	2389	356	5
Sheet1

Book1
	A	B	C	D
1	2022 Clients	2022 Data	2022 Data2	2022 Data3
2	Peter	6543	23	0
3	Sally	9854	23	56
4	Michelle	1265	1256	64
5	Anna	7834	23	0
6	Ben	1234	56	34
7	Michael	1234	1234	2345
8	David	1276	1234	3
9	Anthony	1234	23	0
10	Sue	8765	34	78
11	Sarah	4321	54	67
12	Mary	9876	0	34
13	Rachel	5678	12	45
14	Monica	2356	45	45
15	Charles	1234	0	0
16	Elizabeth	6543	0	78
17	Wong	1276	23456	12
18	Jay	4325	8765	89
19	Jennifer	1234	8765	56
20	Ruba	3254	23	9
21	Henry	9876	78	0
22	Chloe	3245	34567	6
23	Candy	2389	356	5
Sheet2

 

[jasonb75]

Give this a quick try and see if it works correctly for you, I'm still getting the same error with Peter being copied to the wrong list but can see no reason why it is happening. There is no obvious reason for it to be happening so I'm wondering if it's a problem on my laptop as it has been crashing a lot recently.

Option Explicit
Sub CopyClientList()

' Declare variables

Dim ws1 As Worksheet, ws2 As Worksheet
Dim ClientFound As Range, ClientList As Range, NextClient As Range, OldClients As Range
Dim EndOfList As Long, EndOfOld As Long
Dim Clients2022 As Range, Clients2021 As Range

' Set worksheet objects

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

' find last row for each range to be used

EndOfList = ws1.Cells(Rows.Count, 1).End(xlUp).Row
EndOfOld = ws1.Cells(Rows.Count, 5).End(xlUp).Row

' set tables that need to be searched as range objects

Set ClientList = ws1.Range("A2:A" & EndOfList)
Set OldClients = ws1.Range("E5:E" & EndOfOld)

' loop through the names in the 2022 list

For Each NextClient In ClientList

    ' check if client is found in the 2021 list
    
    Set ClientFound = OldClients.Find(NextClient, , xlValues, xlWhole, xlByRows, , False, False, False)
    
        ' assign client to temporary 2021 or 2022 lists based on result of search above
        
        If Not ClientFound Is Nothing Then
            If Clients2021 Is Nothing Then Set Clients2021 = NextClient.Resize(, 4) Else Set Clients2021 = Union(Clients2021, NextClient.Resize(, 4))
        Else
            If Clients2022 Is Nothing Then Set Clients2022 = NextClient.Resize(, 4) Else Set Clients2022 = Union(Clients2022, NextClient.Resize(, 4))
        End If
Next NextClient
    
    ' copy the client lists to sheet 2 by year
    
Clients2021.Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
Clients2022.Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)

End Sub

 

[MARK858]

so I'm wondering if it's a problem on my laptop as it has been crashing a lot recently

I don't think it is as I get the same result with the code.