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

 

[Alex Blakenburg]

Just curious, did you actually try my code in Post #16 ?

 

[Noni]

Just curious, did you actually try my code in Post #16 ?

yes it works. Thank you!!!

 

[Peter_SSs]

yes it works. Thank you!!!

Probably would have been a good idea to try it way back then?

 

[Noni]

totally agree with you! After completing my certification, this is my first project and I've learnt so much ..and really appreciate this wonderful platform. Everyone is amazing and extremely helpful!!!! thank you all!!

 

[Noni]

From Sheet1, 2021 clients data (E:H) needs to be copied to Sheet2 (E:H).

In columns E:H Sheet1, orange highlighted ones are those who are still with company so their data (E:H- from Sheet1) will be copied to Sheet 2 (E:H).

In Columns E:H Sheet1, non-highlighted ones are not with business anymore, so their data is not required in Sheet2.

How coding will be done? please help!

WorksheetDummy.xlsm
	A	B	C	D	E	F	G	H
1	2022 Clients	2022 Data	2022 Data2	2022 Data3	2021 Clients	2021 Data	2021 Data2	2021 Data3
2	Michael	1234	1234	2345	James	2	3	1
3	Sarah	4321	54	67	Sam	3	4	3
4	Mary	9876	0	34	Peter	12	5	1
5	Rachel	5678	12	45	Peter	13	5	2
6	Anna	7834	23	0	Shaw	4	4	0
7	Monica	2356	45	45	Sally	14	345	1
8	Charles	1234	0	0	Michelle	13	6	2
9	Peter	6543	23	0	Ivona	5	4	3
10	Anthony	1234	23	0	Anna	345	3	4
11	Ben	1234	56	34	Claire	4	6	4
12	Elizabeth	6543	0	78	Ben	56456	1	5
13	Wong	1276	23456	12	Ben	5435	2	6
14	Sally	9854	23	56	Michael	34346	3	78
15	Ben	12	34	3	David	54654	3	9
16	Jay	4325	8765	89	Annaleise	0	4	87
17	Anthony	1	2	3	Russell	34656	4	78
18	Michelle	1265	1256	64	Chris	5	1	5
19	David	1276	1234	3	Beth	4	1	56
20	Jennifer	1234	8765	56	John	3	2	35
21	Anthony	98	9	7	Bob	3	2	456
22	Sue	8765	34	78	Anthony	546546	4	567
23	Ruba	3254	23	9	Anthony	3	5	67
24	Henry	9876	78	0	Josh	3	5	67
25	Chloe	3245	34567	6	Sue	3	65	75
26	Anna	5	6	7	Choung	4	7	7
27	Candy	2389	356	5	Robin
28	Monica	4656	1	2
29	Peter	7	6	8
30	Joe	2345	34	1
31	Peter	56	6	4
32
Sheet1

WorksheetDummy.xlsm
	A	B	C	D	E	F	G	H
1	2022 Clients	2022 Data	2022 Data2	2022 Data3	2021 Clients	2021 Data	2021 Data2	2021 Data3
2					Peter	12	5	1
3					Peter	13	5	2
4					Sally	14	345	1
5					Michelle	13	6	2
6					Anna	345	3	4
7					Ben	56456	1	5
8					Ben	5435	2	6
9					Michael	34346	3	78
10					David	54654	3	9
11					Anthony	546546	4	567
12					Anthony	3	5	67
13					Sue	3	65	75
Sheet2

 

[Alex Blakenburg]

Try this. After the End With at the end of the sort and Before the Delete column add the code in blue below:

    shtNew.Sort.SortFields.Clear
    shtNew.Sort.SortFields.Add2 key:=rngNew.Columns(lcolNew) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange rngNew
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ' XXX Additional code to colour code the old and new clients
    Dim rowFirstNew As Long
    Dim lrowNew As Long
    
    lrowNew = shtNew.Cells(Rows.Count, lcolNew).End(xlUp).Row
    With Application
        rowFirstNew = .IfError(.Match(999999, rngNew.Columns(lcolNew), 0), 0)
        If rowFirstNew <> 0 Then
            rngNew.Range(Cells(1, 1), Cells(rowFirstNew - 1, lcolNew)).Interior.Color = 14083324
            rngNew.Range(Cells(rowFirstNew, 1), Cells(lrowNew, lcolNew)).Interior.Color = 15123099
        End If
    End With
    ' XXX End of additional code
    
    rngNew.Columns(lcolNew).EntireColumn.Delete

 

[Noni]

Try this. After the End With at the end of the sort and Before the Delete column add the code in blue below:

    shtNew.Sort.SortFields.Clear
    shtNew.Sort.SortFields.Add2 key:=rngNew.Columns(lcolNew) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange rngNew
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ' XXX Additional code to colour code the old and new clients
    Dim rowFirstNew As Long
    Dim lrowNew As Long
    
    lrowNew = shtNew.Cells(Rows.Count, lcolNew).End(xlUp).Row
    With Application
        rowFirstNew = .IfError(.Match(999999, rngNew.Columns(lcolNew), 0), 0)
        If rowFirstNew <> 0 Then
            rngNew.Range(Cells(1, 1), Cells(rowFirstNew - 1, lcolNew)).Interior.Color = 14083324
            rngNew.Range(Cells(rowFirstNew, 1), Cells(lrowNew, lcolNew)).Interior.Color = 15123099
        End If
    End With
    ' XXX End of additional code
   
    rngNew.Columns(lcolNew).EntireColumn.Delete

it is giving run time error at
"rngNew.Range(Cells(1, 1), Cells(rowFirstNew - 1, lcolNew)).Interior.Color = 14083324"

Sub CopyOldNewClients()

Dim shtOld As Worksheet, shtNew As Worksheet
Dim rngOld As Range, rngLookup As Range, rngNew As Range
Dim lrowOld As Long, lrowLookup As Long, lcolNew As Long
Application.ScreenUpdating = False

Set shtOld = Worksheets("Sheet1")
Set shtNew = Worksheets("Sheet2")

lrowOld = shtOld.Range("A" & Rows.Count).End(xlUp).Row
Set rngOld = shtOld.Range("A2:D" & lrowOld)
lrowLookup = shtOld.Range("E" & Rows.Count).End(xlUp).Row
Set rngLookup = shtOld.Range("E2:E" & lrowLookup)


rngOld.Copy
shtNew.Range("A2").PasteSpecial

lcolNew = rngOld.Columns.Count + 1
Set rngNew = shtNew.Range(rngOld.Address).Resize(, lcolNew)

rngNew.Columns(lcolNew).Formula = "=IfError(Match(" & rngNew.Cells(1, 1).Address(0, 1) & "," & rngLookup.Address(external:=True) & ", 0), 999999)"
rngNew.Columns(lcolNew).Value = rngNew.Columns(lcolNew).Value

Set rngNew = rngNew.Offset(-1).Resize(rngNew.Rows.Count + 1)

shtNew.Sort.SortFields.Clear
shtNew.Sort.SortFields.Add2 Key:=rngNew.Columns(lcolNew) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange rngNew
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' XXX Additional code to colour code the old and new clients
Dim rowFirstNew As Long
Dim lrowNew As Long

lrowNew = shtNew.Cells(Rows.Count, lcolNew).End(xlUp).Row
With Application
rowFirstNew = .IfError(.Match(999999, rngNew.Columns(lcolNew), 0), 0)
If rowFirstNew <> 0 Then
rngNew.Range(Cells(1, 1), Cells(rowFirstNew - 1, lcolNew)).Interior.Color = 14083324
rngNew.Range(Cells(rowFirstNew, 1), Cells(lrowNew, lcolNew)).Interior.Color = 15123099
End If
End With
' XXX End of additional code

rngNew.Columns(lcolNew).EntireColumn.Delete

shtNew.Activate
shtNew.Range("A1").Select

Application.CutCopyMode = False
Application.ScreenUpdating = False

End Sub

 

[Alex Blakenburg]

Do get a message box ? Can you show me a picture ?

 

[Noni]

Do get a message box ? Can you show me a picture ?

Run time error '1004: Application-defined or object error

 

[Noni]

Run time error '1004: Application-defined or object error