VBA to CUT and paste to a different sheet

[abrig005]

I'm trying to figure out how to cut and paste ONLY duplicate names in Column A to a new sheet.

In the attached Excel example, the highlighted rows in Sheet 1 have duplicates in Column A.

Sheet 2 shows just the duplicate values COPIED from Sheet 1.

Note: I left the highlighted rows in Sheet 1 to show the example, but they should not be there after the VBA code is run.

 

[rpaulson]

try this on a COPY of your file.

Sub Move_Them()

Dim rs As Worksheet
Set rs = Worksheets("Sheet2")
l_Row = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To l_Row

If WorksheetFunction.CountIf(Range("A:A"), Cells(r, "A")) > 1 Then
    lr = rs.Cells(Rows.Count, "A").End(xlUp).Row + 1
    Rows(r).Copy Destination:=rs.Rows(lr)
    Cells(r, "B") = "DELETE"
End If

Next r

'delete old records
On Error Resume Next
    With Range("B2:B" & l_Row)
        .Replace "DELETE", False, xlWhole
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With
On Error GoTo 0

End Sub