excelvbaduplicatesworksheetmultiple-entries

Compare and Add data from one worksheet to another in VBA Excel


How can I add rows of data from one worksheet to another after comparing it with each other for existing entries? I have tried doing it, but the only problem is that there are duplicate entries which are being added to it.

For Each Cell1 In Worksheets(1).Range("A1", Worksheets(1).Range("A1").End(xlDown))
        
        For Each Cell3 In Worksheets(3).Range("A1", Worksheets(3).Range("A1").End(xlDown))
    
            If Not Cell1 = Cell3 Then
                Dim LastRow3 As Long
                LastRow3 = Worksheets(3).Range("A1").SpecialCells(xlCellTypeLastCell).Row + 1
                Worksheets(3).Range("A1:C1").Rows(LastRow3).Value = Worksheets(1).Range("A1:C1").Rows(Cell1.Row).Value
                
            Else
                Exit For
            End If
    
        Next Cell3

Next Cell1


Solution

  • Using Find() is easier to manage than using a nested loop:

    Sub Test()
    
        Dim c As Range, f As Range
        Dim ws1, ws3
    
        Set ws1 = Worksheets(1)
        Set ws3 = Worksheets(3)
    
        For Each c In ws1.Range(ws1.Range("A1"), ws1.Cells(Rows.Count, 1).End(xlUp)).Cells
    
            Set f = ws3.Range(ws3.Range("A1"), _
                               ws3.Cells(Rows.Count, 1).End(xlUp)).Find( _
                                          What:=c.Value, lookat:=xlWhole)
    
            If f Is Nothing Then
                ws3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, 3).Value = _
                                                            c.Resize(1, 3).Value
            End If
    
        Next c
    
    End Sub