compare and copy

Ralter649 · Apr 16, 2018

I am comparing each cell in 1 column to each cell in a 2nd column (ex: C5:C500 to R5:R100) and if they match copy A:M of the row that C was on to SHEET4. Then move on the next row. I have been getting help and now getting frustrated and running out of time. Here is what I have. How does it look and how would I copy the line to “Sheet 4”? THANKS

Dim Rng As Range, Dn As Range, n As Long, Lst As Long, Dic As Object
Set Rng = Range("R1", Range("R" & Rows.Count).End(xlUp))
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare

For Each Dn In Rng: Dic(Dn.Value) = Empty: Next
Lst = Range("C" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
For n = Lst To 1 Step -1
With Range("C" & n)
If Dic.exists(.Value) Then (COPY A:M OF ROW THAT C WAS ON TO SHEET 4)

End With
Next n
Application.ScreenUpdating = True
End Sub

Fluff · Apr 16, 2018

How about

Code:

Sub CopyDupe()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = vbTextCompare
      For Each Cl In Range("R1", Range("R" & Rows.count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Range("C1", Range("C" & Rows.count).End(xlUp))
         If .exists(Cl.Value) Then
            Cl.Offset(, -2).Resize(, 13).Copy Sheets("Sheet4").Range("A" & Rows.count).End(xlUp).Offset(1)
         End If
      Next Cl
   End With
End Sub

Ralter649 · Apr 16, 2018

I get an run 'run-time error 9' for this line....

Cl.Offset(, -2).Resize(, 13).Copy Sheets("Sheet4").Range("A" & Rows.count).End(xlUp).Offset(1)

Fluff · Apr 16, 2018

Do you have a sheet called "Sheet4"?

compare and copy

Ralter649

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Ralter649

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

compare and copy

Ralter649

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

Ralter649

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock