VBA - copying unique values into different sheet

Question

Hoping you can help, please!

So I have 2 worksheets, 1 & 2. Sheet1 has already existing data, Sheet2 is used to dump raw data into. This is updated daily, and the data dump includes both data from previous days, as well as new data. The new data may include rows relating to interactions that may have happened earlier in the month, not just the previous day. So the data is not "date sequential".

There are 9 columns of data, with a unique identifier in column I.

What I'm needing to happen is when running the macro, it looks in column I in Sheet1 and Sheet2, and only copies and pastes rows where the unique identifier in Sheet 2 doesn't already exist in Sheet1. And pastes them from the last empty row onwards in Sheet1.

What I currently have is this - it's all I could find online:

Sub CopyData()

Application.ScreenUpdating = False

Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim rng As Range
Dim foundVal As Range
For Each rng In Sheets("Sheet2").Range("A1:I" & LastRow)
    Set foundVal = Sheets("Sheet1").Range("I:I").Find(rng, LookIn:=xlValues, LookAt:=xlWhole)
    If foundVal Is Nothing Then
        rng.EntireRow.Copy Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
Next rng
Application.ScreenUpdating = True 
End Sub

But it's just not working - not only does it not recognise if the value in column I already exists, it's copying and pasting only the first 2 rows from Sheet2, but duplicating them 8 times each!

Apologies in advance, I'm a real VBA novice, and just can't work out where it's all going wrong. I would appreciate any assistance!

Fernando J. Rivera Fernando J. Rivera · Accepted Answer · 2017-11-29T23:38:46

This will do what you want:

Sub testy()
    Dim wks As Worksheet, base As Worksheet
    Dim n As Long, i As Long, m As Long
    Dim rng As Range

    Set wks = ThisWorkbook.Worksheets(2) 'Change "2" with your input sheet name
    Set base = ThisWorkbook.Worksheets(1) 'Change "1" with your output sheet name

    n = base.Cells(base.Rows.Count, "A").End(xlUp).Row
    m = wks.Cells(wks.Rows.Count, "A").End(xlUp).Row

    For i = 2 To m
        On Error Resume Next
        If IsError(WorksheetFunction.Match(wks.Cells(i, 9), base.Range("I:I"), 0)) Then
            Set rng = wks.Cells(i, 1).Resize(1, 9) 'Change 9 with your input range column count
            n = n + 1
            base.Cells(n, 1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
        End If
    Next i

End Sub

VBA - copying unique values into different sheet

1 Answers