VBA code to compare two columns in different worksheets

[Wafee]

Hi, I have below code where I am trying to compare two columns of data in different worksheets and if anything new is found then it has been added above last row of sheet1 in column "A". the below code is only adding empty row. can someone help me with this.

It has to basically compare column A of both sheets and if there is a value in pivot sheet which is not found in sheet1 then that needs to added above the "lastRow1".

[CODE=vba][CODE=vba][CODE=vba][CODE=vba][CODE=vba]Sub Compare()

Dim lastRowE As Integer
Dim lastRowF As Integer
Dim lastRowM As Integer
Dim foundTrue As Boolean

Application.ScreenUpdating = False

lastRow1 = Sheets("sheet1").Cells(Sheets("sheet1").Rows.Count, "A").End(xlUp).Row
lastRow2 = Sheets("Pivot").Cells(Sheets("Pivot").Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow2
foundTrue = False
For j = 1 To lastRow1

    If Sheets("Pivot").Cells(i, 1).Value = Sheets("sheet1").Cells(j, 1).Value Then
        foundTrue = True
        Exit For
    End If

Next j

If Not foundTrue Then

    Sheets("Pivot").Cells(i).Copy Destination:= _
    Sheets("sheet1").Rows(lastRow1 - 1)

End If

Next i

Application.ScreenUpdating = True

End Sub

[/CODE][/CODE][/CODE][/CODE][/CODE]

 

[TotallyConfused]

Hello Wafee

I'm certainly not an expert with Excel or VBA, but I think your problem is that you have NOT inserted a blank row above your last row. With that in mind, try this:

If not foundTrue then
    Sheets("Sheet1").Rows(LastRow1).EntireRow.Insert Shift:=xlShiftDown
    Sheets("Pivot").Cells(i).Copy Destination:= _
    Sheets("Sheet1").Cells(LastRow1,1)
End If

Added Note: While your program will work without doing so, it would be a good idea to 'Dim LastRow1' and 'LastRow2'.
I hope my code solves your problem.

TotallyConfused