How to copy rows to the next sheet if a condition is met?

[Noni]

Hi there,

Data is in Sheet1 and Sheet2 is where I am copying rows if a condition is met.

The data (cell range) that need to be copied is in B:F (2022 client data).

If the last year’s client is also a client in this year 2022 then I want to move that client’s detail (which is in B:F) to Sheet2. Once all the last year’s 2021 clients are checked then copy new clients (2022) data to Sheet 2.

Below: blue highlighted rows are those which are clients in both 2021 and 2022 so their data gets copied first in Sheet2. Green highlighted ones are new clients, so their details get copied after in Sheet2. And in column A, non-highlighted ones are no longer our client, so we don’t copy them to Sheet2.

It is also important to keep the same order. By this I mean, that Sheet2 must have the old client details (which have met the above condition) first and in the same order as they appear in Sheet1 column A. Then new clients’ details go at the bottom of Sheet2. i.e. Peter who is a client in 2021-2022 gets copied first in sheet2. Then Sally goes to Sheet2, and then Michelle ….

Sheet 1

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

Sheet 2

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

 

[Fluff]

Duplicate to: copying rows to other sheet if a condition is met- by using VBA code

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.