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

Noni

Board Regular
Joined
Aug 27, 2022
Messages
63
Office Version
  1. 2021
Platform
  1. Windows
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.

VBA Code:
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
ABCDE
12022 Clients2022 Data2022 Data22022 Data32021 Clients
2Michael123412342345James
3Sarah43215467Sam
4Mary9876034Peter
5Rachel56781245Shaw
6Anna7834230Sally
7Monica23564545Michelle
8Charles123400Ivona
9Peter6543230Anna
10Anthony1234230Claire
11Ben12345634Ben
12Elizabeth6543078Michael
13Wong12762345612David
14Sally98542356Annaleise
15Jay4325876589Chris
16Michelle1265125664John
17David127612343Bob
18Jennifer1234876556Anthony
19Sue87653478Sue
20Ruba3254239Robin
21Henry9876780
22Chloe3245345676
23Candy23893565
Sheet1

Worksheets.xlsm
ABCD
12022 Clients2022 Data2022 Data22022 Data3
2Peter6543230
3Sally98542356
4Michelle1265125664
5Anna7834230
6Ben12345634
7Michael123412342345
8David127612343
9Anthony1234230
10Sue87653478
11Sarah43215467
12Mary9876034
13Rachel56781245
14Monica23564545
15Charles123400
16Elizabeth6543078
17Wong12762345612
18Jay4325876589
19Jennifer1234876556
20Ruba3254239
21Henry9876780
22Chloe3245345676
23Candy23893565
Sheet2
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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!!
 
Upvote 0
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
ABCDEFGH
12022 Clients2022 Data2022 Data22022 Data32021 Clients2021 Data2021 Data22021 Data3
2Michael123412342345James231
3Sarah43215467Sam343
4Mary9876034Peter1251
5Rachel56781245Peter1352
6Anna7834230Shaw440
7Monica23564545Sally143451
8Charles123400Michelle1362
9Peter6543230Ivona543
10Anthony1234230Anna34534
11Ben12345634Claire464
12Elizabeth6543078Ben5645615
13Wong12762345612Ben543526
14Sally98542356Michael34346378
15Ben12343David5465439
16Jay4325876589Annaleise0487
17Anthony123Russell34656478
18Michelle1265125664Chris515
19David127612343Beth4156
20Jennifer1234876556John3235
21Anthony9897Bob32456
22Sue87653478Anthony5465464567
23Ruba3254239Anthony3567
24Henry9876780Josh3567
25Chloe3245345676Sue36575
26Anna567Choung477
27Candy23893565Robin
28Monica465612
29Peter768
30Joe2345341
31Peter5664
32
Sheet1

WorksheetDummy.xlsm
ABCDEFGH
12022 Clients2022 Data2022 Data22022 Data32021 Clients2021 Data2021 Data22021 Data3
2Peter1251
3Peter1352
4Sally143451
5Michelle1362
6Anna34534
7Ben5645615
8Ben543526
9Michael34346378
10David5465439
11Anthony5465464567
12Anthony3567
13Sue36575
Sheet2
 
Upvote 0
Try this. After the End With at the end of the sort and Before the Delete column add the code in blue below:

Rich (BB code):
    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
 
Upvote 0
Try this. After the End With at the end of the sort and Before the Delete column add the code in blue below:

Rich (BB code):
    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
 
Upvote 0
Run time error '1004: Application-defined or object error
1663371796058.png
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top