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

[Noni]

If in Sheet1, A2 exists in B2 then copy B2:F2 to Sheet2. If it doesn’t exist then ignore A2 and jump to A3 and check if it exists in B2. If True then copy B2:F2 to Sheet2, False then jump to A4………. Once this condition is run on all cells of Col A, then copy the remaining of Col B:F to Sheet 3.

In other words:

If 2022 client is also 2023 client then I want to move this old client’ detail to the next sheet. Once done checking 2022 clients then move new clients (2023)to sheet 3. Sheets 3 contains old clients(2022 and 2023) first then new clients(2023) details.

When we have run the above condition on all 2022 client addresses (in this example A2:A20), then copy the remaining 2023 client details at the bottom of Sheet2.

It is also important to keep the same order. By this I mean, that the table in Sheet3 must have the old client details (which have met the above condition) first and in the same order as they appear in Sheet1 A column. Then new clients’ details go to the bottom of the table in Sheet3.


Below is Sheet 1

VBA.xlsx
	A	B	C	D	E	F
1	2022 client address	2023 client Address	2023 client BB	2023 client Cons	2023 client Bld	2023 client Other
2	9 Millow Road Alba VIC 7609	215/20b Lexi Dr Bella Otta VIC 9087	1234	1234	2345	12
3	34 Queen Street Kucha VIC 4567	7 Winter Street VIC 6978	4321	54	67	34
4	12 Winter Ave TSA 9876	Cnr Hunter Road QLD 3045	9876	0	34	0
5	76 Cat Street Martha QLD 3456	56 Spring Cres QLD 8767	5678	12	45	877
6	7 Queen Cres NSW 7654	12 Winter Ave TSA 9876	7834	23	0	345
7	9/54 Dairy Ave TSA 1234	3C Villa Street QLD 7686	2356	45	45	1245
8	76 Green Square Bella NSW 6547	8 Auburn Cres VIC 6543	1234	0	0	0
9	3 Stanley Street Chilom TAS 7986	56 Spring Cres QLD 8767	6543	23	0	0
10	56 Hunter Road Chilom ACT 1234	9/54 Dairy Ave TSA 1234	1234	23	0	0
11	56 Spring Cres QLD 8767	7 Queen Cres NSW 7654	1234	56	34	0
12	436 Power Street Quon DW 8765	Cnr Hunter Road QLD 3045	6543	0	78	45
13	215/20b Lexi Dr Bella Otta VIC 9087	322 Lizardwood Road Maccarthur SA 0987	1276	23456	12	67
14	1234 Byor Road Narro VIC 2345	Klirnc Avenue BALLA BALLA DW 1234	9854	23	56	89
15	6/8 Ryan Street Barrow QLD 7654	10 Somerset Street Aaron QLD 1245	4325	8765	89	34
16	7 Queen Cres NSW 7654	3 Stanley Street Chilom TAS 7986	1265	1256	64	56
17	12 Alroy Road NSW 1234	436 Power Street Quon DW 8765	1276	1234	3	23
18	Emu Pow/Quor Valley Way. Darra ACT 7654	1438 Alma Park Berro NSW 1209	1234	8765	56	56
19	Klirnc Avenue BALLA BALLA DW 1234	Emu Pow/Quor Valley Way. Darra ACT 7654	8765	34	78	34
20	56 Spring Cres QLD 8767	Bethel Road Wong SA 0987	3254	23	9	78
21		98 Campbell Court Darrek QLD 5647	9876	78	0	0
22		Filo Centre Campo DC VIC 1234	3245	34567	6	0
23		22 Watson Ave PURNER VIC 1234	2389	356	5	0
Sheet1

Sheet 2

VBA.xlsx
	A	B	C	D	E
1	2023 client Address	2023 client BB	2023 client Cons	2023 client Bld	2023 client Other
2	12 Winter Ave TSA 9876	7834	23	0	345
3	7 Queen Cres NSW 7654	1234	56	34	0
4	9/54 Dairy Ave TSA 1234	1234	23	0	0
5	3 Stanley Street Chilom TAS 7986	1265	1256	64	56
6	56 Spring Cres QLD 8767	6543	23	0	0
7	436 Power Street Quon DW 8765	1276	1234	3	23
8	215/20b Lexi Dr Bella Otta VIC 9087	1234	1234	2345	12
9	Emu Pow/Quor Valley Way. Darra ACT 7654	8765	34	78	34
10	Klirnc Avenue BALLA BALLA DW 1234	9854	23	56	89
11	7 Winter Street VIC 6978	4321	54	67	34
12	Cnr Hunter Road QLD 3045	9876	0	34	0
13	56 Spring Cres QLD 8767	5678	12	45	877
14	3C Villa Street QLD 7686	2356	45	45	1245
15	8 Auburn Cres VIC 6543	1234	0	0	0
16	Cnr Hunter Road QLD 3045	6543	0	78	45
17	322 Lizardwood Road Maccarthur SA 0987	1276	23456	12	67
18	10 Somerset Street Aaron QLD 1245	4325	8765	89	34
19	1438 Alma Park Berro NSW 1209	1234	8765	56	56
20	Bethel Road Wong SA 0987	3254	23	9	78
21	98 Campbell Court Darrek QLD 5647	9876	78	0	0
22	Filo Centre Campo DC VIC 1234	3245	34567	6	0
23	22 Watson Ave PURNER VIC 1234	2389	356	5	0
Sheet2

 

[Noni]

@Peter_SSs

 

[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

Sheet2

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