CODEWITHSUNDEEP
vbaexcel
user3720702
user3720702

Reputation: 303

Compare 2 columns in 2 workbooks, copy matched row if match is found

I have two workbooks (or 2 sheets): Workbook A and Workbook B. I want to compare: Columns B and C in Workbook A WITH Columns A and B in Workbook B If a Match is found THEN I need to copy the MATCHED row from Workbook B and paste it onto the MATCHED row on workbook A. In other words: I need to copy the cell values of Columns C and D of the matched row of Workbook B onto cells of columns D and E of matched row in Workbook A.

What I have so far only compares the 2 columns which I'm hoping is correct. The code below is for 2 sheets instead of two workbooks:

Sub compareNcopy()

Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet

Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets(2): Set sh3 = Sheets(3)


Dim i As Long, j As Long,
Dim lr1 As Long, lr2 As Long
Dim nxtRow As Long
Dim rng1 As Range, rng2 As Range, rng3 As Range

lr1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = sh2.Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lr1
    Set rng1 = sh1.Range("A" & i)

    For j = 1 To lr2
        Set rng2 = sh2.Range("A" & j)

        If StrComp(CStr(rng1.Value), CStr(rng2.Value), vbTextCompare) = 0 Then

            If rng1.Offset(0, 1).Value = rng2.Offset(0, 1).Value Then

            End If

        End If
        Set rng2 = Nothing
    Next j
    Set rng1 = Nothing
Next i
End Sub

Help would be much appreciated.

Upvotes: 3

Views: 2892

Answers (1)

Jacob Lambert
Jacob Lambert

Reputation: 7679

To copy between 2 sheets (or even two books) is pretty much the same as if you were copying to another cell in the same sheet (or book), you just have to specify which sheet (or book). What you would want to do is something along the lines of:

sh2.Cells(j,3).Resize(1,2).Copy Destination:=sh1.Cells(i,3).Resize(1,2)

This is for if the data you want to copy is found in sh2. If it is the other way around, switch the sh2 and sh1, and the j and i.

If you want to copy between workbooks, you will need to add Workbooks(wb1). in front of the Sheets(sh2). specifier, with wb1 being the workbook variable.

EDIT: Since sh2 in essence is Sheets(2) what I had previously shown was Sheets(Sheets(2)) which makes no sense and that is why the error was popping up. My apologies. Instead of using Sheets(sh2) just use sh2, and the same goes for sh1. I have fixed the above code to reflect this.

Upvotes: 2

Related Questions