Compare values from two workbooks, if values match in certain columns then copy across value from a different column

[Tom Allen]

Hi I have had a look online but I couldn't find any similar to what I am looking to achieve that I would be able to modify.

I have two workbooks.

I need to compare the values in columns B from both of these workbooks, if the values match then copy the value from the cell in column A from workbook 2 to column C in workbook 1.

Workbook 1 will be open already, workbook 2 doesn't need to remain open after this macro has finished.

Any help would be greatly appreciated.

 

[Tom Allen]

I'm trying to get the following code to work but I'm getting a compile error: type mismatch. Any thoughts on what the problem is?

Sub copyData()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range
Set wb1 = "disk usage.xlsx" 'Use actual name instead of index, eg. "Data.xlsx"
Set wb2 = HostingClients.xlsx 'same here
Set sh1 = wb1.Sheets(1) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
    For Each c In sh1.Range("B2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                sh1.Range("C" & c.Row) = sh2.Range("A" & fn.Row).Value
            End If
    Next
End Sub

 

[Fluff]

Try

Set wb1 = Workbooks("disk usage.xlsx")

& the same for the other workbook