Excel VBA: Im trying to compare data between books with some condition to data and transfer only unmatched data second book

Question

book1  sheet1 data as following;

Rows: from 11 to 17
Column B: 101, 102, 103, 104, 105, 106, 107
Column C: test1, test2, test3, test4, test5, test6, test7
Column D: 12/1/15, 12/2/15, 12/3/15, 12/4/15, 12/5/15, 12/6/15, 12/7/15
Column E: 12/7/15, 12/7/15, 12/8/15, 12/10/15, 12/2/15, 11/30/15, 12/15/15

J11: $45.00
J16: $90.00
K12: $50.00, K13: $100.00, K14: $45.0C
L15: $50.00
M14: $45.00, M17: $250.00

book2  sheet1 data should be as following;

Row: 4 to 7
Column A: 12/2/15, 12/3/15, 12/4/15, 12/7/15
Column B: 12/7/15, 12/8/15, 12/10/15, 12/15/15
Column C: test2, test3, test4, test7
Column H: 102, 103, 104, 107
Column I: $50.00, $100.00, $90.00, $250.00

What Im trying here is that I want to see if “K11” or “M11” is > then $0 in book1/sheet1, if yes then compare the value of “C11” and “E11” from book1/sheet1 to list of Column C and Column B in book2/sheet1. IF the both values are same in Column C and Column B in book2/sheet1 then check for E12 in book1/sheet1 and continue. And if the value did not match in Column C and Column B in book2/sheet1 then I want to copy D11 to A4, E11 to B4, C11 to C4, K11&M11 to I4 and B11 to H4 from book1/sheet1 to book2/sheet1 and continue in next empty row in book2/sheet1.

So the code should only get the data from rows 12 to 14 and 17 from book1/sheet1 and put that in book2/sheet1 in rows 4 to 7. Im running following code but its not copying anything.

Sub test5()
Dim lrow1 As Long
Dim lrow2 As Long
Dim erow As Long
Dim name1 As String
Dim name2 As String
Dim mydate1 As Date
Dim mydate2 As Date
Dim check As Boolean

Workbooks.Open Filename:="C:\Users\tp142d\Documents\Book2.xlsx"

lrow1 = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
lrow1 = Workbooks("Book2").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 11 To lrow1
    name1 = Sheets("Sheet1").Cells(i, "C").Value
    mydate1 = Sheets("Sheet1").Cells(i, "E").Value
    check = False

    For j = 3 To lrow2
        name2 = Workbooks("Book2").Sheets("Sheet1").Cells(j, "C").Value
        mydate2 = Workbooks("Book2").Sheets("Sheet1").Cells(j, "B").Value

        If Sheets("Sheet1").Cells(i, "K") > 0 And Sheets("Sheet1").Cells(i, "M") > 0 And name1 <> name2 And mydate1 <> mydate2 Then
            check = True
        End If

    Next j

    If Not check Then
        Sheets("Sheet1").Cells(i, "D").Copy
        erow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        Workbooks("Book1").Sheets("sheet1").Cells(erow, "A").PasteSpecial

        Sheets("Sheet1").Cells(i, "E").Copy
        Workbooks("Book2").Sheets("Sheet1").Cells(erow, "B").PasteSpecial

        Sheets("Sheet1").Cells(i, "C").Copy
        Workbooks("Book2").Sheets("Sheet1").Cells(erow, "C").PasteSpecial

        Sheets("Sheet1").Cells(i, "B").Copy
        Workbooks("Book2").Sheets("Sheet1").Cells(erow, "H").PasteSpecial

        Sheets("Sheet1").Cells(i, "K").Copy
        Workbooks("Book2").Sheets("Sheet1").Cells(erow, "I").PasteSpecial

    ActiveWorkbook.Save

    End If

Next i

End Sub

Answer 1

The clue is in your title: Compare data between two workbooks.

Your code is comparing different cells all on the same workbook. The reason is that you haven't referred to the Book1 object explicitly and, without an explicit WorkBook object, VBA will assume the ActiveWorkBook. When you open a WorkBook (like this, for example, Workbooks.Open Filename:="C:\Users\tp142d\Documents\Book2.xlsx"), it becomes the ActiveWorkbook.

I'm afraid you're comparing data in Book2 with ... data in Book2.

To prevent that, lines like these

name1 = Sheets("Sheet1").Cells(i, "C").Value

need to include the WorkBook object, like so:

name1 = WorkBooks("Book1").Sheets("Sheet1").Cells(i, "C").Value

Easier would be to set two WorkBook object variables - easier to type, easier for intellisense, and easier to read. I'd also do the same with the Sheet names too. Using your naming protocol, some sample code would look like this:

Dim wb1 As WorkBook
Dim wb2 As WorkBook
Dim ws1 As WorkSheet
Dim ws2 As WorkSheet

'Define your workbooks
Set wb1 = WorkBooks("Book1")
Set wb2 = WorkBooks.Open Filename:="C:\Users\tp142d\Documents\Book2.xlsx"

'Define your worksheets
Set ws1 = wb1.WorkSheets("Sheet1")
Set ws2 = wb2.WorkSheets("Sheet1")

'Sample useage
name1 = ws1.Cells(i, "C").Value
name2 = ws2.Cells(j, "C").Value

I have a personal rule always to include the WorkBook and WorkSheet objects, even if I only have one Workbook open. All you need is for a user to activate a different workbook or a different sheet and you've lost control of your code - and something like Cells.Clear could definitely knock you off the user's Christmas card list. In cases where you're comparing data across two workbooks or sheets then it's a must.

By the way, I suspect this line is a typo:

lrow1 = Workbooks("Book2").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Shouldn't that be:

lrow2 = Workbooks("Book2").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

Answer 2

First you really need to look into what @Ambie stated in his answer, as it would have helped to see that you had the copy and paste backwards.

This code should do what you want:

    Sub test5()
Dim lrow1 As Long
Dim lrow2 As Long
Dim erow As Long
Dim name1 As Variant
Dim name2 As Variant
Dim mydate1 As Variant
Dim mydate2 As Variant
Dim check As Boolean
Dim ows As Worksheet
Dim tws As Worksheet
Dim owb As Workbook
Dim twb As Workbook
Dim check2 As Boolean

Set owb = ActiveWorkbook
Set twb = ActiveWorkbook 'Workbooks.Open(Filename:="C:\Users\tp142d\Documents\Book2.xlsx")

Set ows = owb.Sheets("Sheet21")
Set tws = twb.Sheets("Sheet28")

lrow1 = ows.Range("B" & Rows.Count).End(xlUp).Row
lrow2 = tws.Range("A" & Rows.Count).End(xlUp).Row

For i = 11 To lrow1

    name1 = ows.Cells(i, "C").Value
    mydate1 = ows.Cells(i, "E").Value
    check = False
    check2 = False
    If ows.Cells(i, "K") > 0 Or ows.Cells(i, "M") > 0 Then
        check2 = True
        For j = 3 To lrow2
            name2 = tws.Cells(j, "C").Value
            mydate2 = tws.Cells(j, "B").Value

            If name1 = name2 And mydate1 = mydate2 Then
                check = True
                Exit For
            End If

        Next j
    End If
    If Not check And check2 Then
        erow = tws.Cells(tws.Rows.Count, 1).End(xlUp).offset(1, 0).Row
        tws.Cells(erow, "A").Value = ows.Cells(i, "D").Value
        tws.Cells(erow, "B").Value = ows.Cells(i, "E").Value
        tws.Cells(erow, "C").Value = ows.Cells(i, "C").Value
        tws.Cells(erow, "H").Value = ows.Cells(i, "B").Value
        tws.Cells(erow, "I").Value = ows.Cells(i, "K").Value + ows.Cells(i, "M").Value


   ' twb.Save

    End If

Next i

End Sub