Help adapting an existing macro.

[paul1970]

Hi,
First of all this is a great forum and I can honestly say you've all taught me everything I know about VBA (2 whole days experience!).
Anyway, I've modified this macro I found on this forum, all it does is compares 2 lists of 6 digit serial numbers and and then tells me which numbers are in list 1 that are not in list 2.
However, I want to modify it so that it also gives me the numbers in list 2 that are not in list 1 (put into column F). I then need it to cut the matching serial numbers (in columns A and B) from sheet 1 and paste them into sheet 2.

Can anyone help?

Here's the code for what I have so far.

Sub copy_duplicate()
    
    Range("D4:D1000").Select
    Selection.ClearContents
    Range("D4").Select
    
    Dim r As Long
    Dim cr As Long
    r = 4
    cr = 4
    On Error Resume Next
    Do While Sheet1.Cells(r, 1) <> ""
       a = Application.WorksheetFunction.VLookup(Sheet1.Cells(r, 1), Range("B:B"), 1, 0)
       
        If a <> "" Then
            
        Else
            Sheet1.Cells(cr, 4) = Sheet1.Cells(r, 1)
            cr = cr + 1
            
        End If
        a = ""
        r = r + 1
    Loop
End Sub

[martesoft]

Sub copy_duplicate()
    Dim r As Long
    Dim cr As Long
    Dim cr2 As Long
    Dim a As Variant
    
    Sheet1.Range("E4:E1000").ClearContents
    Sheet2.Range("F4:F1000").ClearContents
    
    r = 4
    cr = 4
    cr2 = 4
    On Error Resume Next
    Do While Sheet1.Cells(r, 1) <> ""
        a = Application.WorksheetFunction.VLookup(Sheet1.Cells(r, 1), Range("B:B"), 1, 0)
       
        If a = "" Then
        
            Sheet1.Cells(cr, "F") = Sheet1.Cells(r, 1)
            cr = cr + 1
        Else
        
        
            Sheet2.Cells(cr2, "E") = Sheet1.Cells(r, 1)
            cr2 = cr2 + 1
        End If
        
        a = ""
        r = r + 1
    Loop
End Sub

[royUK]

Paul, please take more care that your Thread Titles convey a better idea of what you want in future. Read the Forum Rules to see why.

[paul1970]

Thanks Martesoft but it's not quite what I was after.

It does copy the duplicate serial numbers across to the second sheet but it doesn't remove them from the first sheet.

How is this done?

[royUK]

Please Edit the Title or I will lock the Thread!

[paul1970]

Sorry, but it's not an easy question to sum up in a couple of words.
Am I posting in the wrong section of the forum? If so, can you tell me where I need to post and then please delete this thread.