why VBA code not working for copying data to next Sheet

[Noni]

In Sheet1, A:D has data about this year’s clients and column E has names of last year’s clients. I want to copy 2022 clients’ data who were with us in the year 2021 as well into Sheet2, in non-filled cells of columns A:D.
Secondly, columns A and E don’t have the same number of filled cells. Let’s say last year we had 23 clients but this year it may increase to 23 or decrease to 15. ( I don’t know below code will take care of this or not)
In the below code, you see the condition in IF statement is if E2=A2, then copy A2:D2 to Sheet2. The logic behind this is the order we need to keep in Sheet2. 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 E. i.e. Peter who is a client in both year 2021-2022 gets copied first in Sheet2. Then Sally goes to Sheet2, and then Michelle ….
Below screenshots: 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(not yet coded). And in column E in Sheet1, non-highlighted ones are no longer our clients, so we don’t copy them to Sheet2.
The VBA code for the above situation is down below. But when I click the command button, nothing happens.
Sheet1

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
24
25
26
27
28
29
30
Sheet1

Sheet2

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
Sheet2

VBA code that is not working

Private Sub CommandButton1_Click()
    
    a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
      
  
    For i = 2 To a
    
        If Worksheets("Sheet1").Cells(i, 5).Value = Worksheets("Sheet1").Cells(i, 2).Value Then
    
            Worksheets("Sheet1").Range("A:A,B:B,C:C,D:D").Copy
    
            Worksheets("Sheet2").Activate
    
    
            b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
     
            Worksheets("Sheet2").Cells(b + 1).Select
     
            ActiveSheet.Paste
     
            Worksheets("Sheet1").Activate
     
     End If
     
    
Next

Application.CutCopyMode = False

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


End Sub

 

[MARK858]

Try..

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

 

[Noni]

Try..

Worksheets("Sheet2").Cells(b + 1, 1).Select[/
[/QUOTE]

it is still not working.

 

[Peter_SSs]

I have not checked the whole code, but for one thing ..

In the below code, you see the condition in IF statement is if E2=A2, then

If Worksheets("Sheet1").Cells(i, 5).Value = Worksheets("Sheet1").Cells(i, 2).Value Then

No, the code is comparing E2 with B2, not A2 and of course E2 and B2 will never be equal.

 

[mohadin]

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 i = 1 To a
        For ii = 1 To aa
            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

 

[Noni]

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 i = 1 To a
        For ii = 1 To aa
            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

@mohadin . I really appreciated your post, but with above code I'm getting below result on Sheet2

Worksheets.xlsm
	A	B	C	D
1	2022 Clients	2022 Data	2022 Data2	2022 Data3
2	Michael	1234	1234	2345
3	Anna	7834	23	0
4	Peter	6543	23	0
5	Anthony	1234	23	0
6	Ben	1234	56	34
7	Sally	9854	23	56
8	Michelle	1265	1256	64
9	David	1276	1234	3
10	Sue	8765	34	78
Sheet2

but the order I 'm looking for in Sheet 2 is

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
Sheet2

Sheet 2: Since Peter is client in 2021 and 2022, his 2022 detail (row A9:D9) gets copied first in sheet2. then Sally is client in both 2021 and 2022 so her data (A14:D14) gets copied next in Sheet2. we need to keep the order in Sheet 2 same as it appears in Column E of Sheet1 ( 2021 Clients).
I hope I'm making sense.

Please amend the code for me, really appreciated

Sheet1

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

 

[mohadin]

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

 

[Noni]

@mohadin Thank you so much!! it worked

Is there a way to copy highlighted green rows (A:D) who have become clients in the year 2022 to get copied next in Sheet2? Do I need to create another command button for this or above code can be modified? could you assist please

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	Ruba	3254	23	9
20	Henry	9876	78	0
21	Chloe	3245	34567	6
22	Candy	2389	356	5
Sheet2