Compare workbooks and transfer data

[Syres]

Guys and girls,

I was wondering if this is even possible but what i have been trying to do is compare cells A1:B6 with the same range in another workbook and if they match transfer the value from that colum c same line to the main workbook.

What i have tryed so far is:
activate a workbook try to set variables and activate another workbook set variable and compare those (did not work)
compare a variable to a cell in a different workbook and grab the value (did not work)

then i copyed some code from the internet and lost my own work..

Could you help me out or gimme a direction?

Regards


Syres

 

[JLGWhiz]

Having a rough go at it, are we? Well, I will just assume that you workbooks are single sheet workbooks, since you failed to mention the sheets. I will also assume that you are trying to run the code from the main workbook, which I will designate as Workbooks(1) . And not having a clue as to the name of the other workbook, I will just refer to it as Workbooks(2) and you can fix the name later.

compare cells A1:B6 with the same range in another workbook and if they match transfer the value from that colum c same line to the main workbook.

This line implies that you are are trying to match row by row. So here goes. This assumes both workbooks are open at run time.

Sub matchNcopy()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, i As Long
Set wb1 = Workbooks(1) 'The main workbook must be opened first
Set wb2 = Workbooks(2)
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
For i = 1 To 6
    With sh1
        If .Cells(i, 1).Value = sh2.Cells(i, 1).Value And .Cells(i, 2).Value = sh2.Cells(i, 2).Value Then
            .Cells(i, 3) = sh2.Cells(i, 3).Value
        End If
    End With
Next
End Sub

You can edit the workbook and sheet names to make sure they match your files. When you replace the workbook or sheet index number with the names, be sure to enclose the names in double quotation marks and for the workbooks, include the file extension, eg. .xlsm or .xlsx.

 

[Syres]

Thanks for your reply JLGWhiz,

I tryed your code but i get an error when he try's to set the workbooks: "Run- time error '424': Object required

I love your approach tough, no use of variables that would complicate the code.

Here is the code applyed to my sheets

Sub matchNcopy()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, i As Long, ii As Long
Set wb1 = Book1.xlsm 'Edit
Set wb2 = Book2.xlsm 'Edit
Set sh1 = wb1.Sheets(1) 'Edit
Set sh2 = wb2.Sheets(1) 'Edit
For i = 2 To 6
    With sh1
        For ii = 2 To 6
          If .Cells(i, 1).value = sh2.Cells(ii, 1).value And .Cells(i, 2).value = sh2.Cells(ii, 2).value Then
            .Cells(i, 3) = sh2.Cells(i, 3).value
          End If
        Next ii
    End With
Next
End Sub

 

[Syres]

Sorry guess it was early this morning.

I fixed the code and it runs now.
Just can't copy the end result on my workplace (Wil do this when i'm home).

Thanks for your fix JLGWhiz

 

[JLGWhiz]

Sorry guess it was early this morning.

I fixed the code and it runs now.
Just can't copy the end result on my workplace (Wil do this when i'm home).

Thanks for your fix JLGWhiz

Set wb1 = Book1.xlsm 'Edit
Set wb2 = Book2.xlsm 'Edit

Difinitely would not work. Glad you got it fixed.
Regards, JLG