Match Criteria in One Workbook with Criteria in Another Workbook

[ozbeachbum]

Hi,
I am trying to retrive data from one workbook, if the criteria in that workbook matches the criteria in the workbook where I want the data to be returned.

I trust the following explanation makes sense.

Workbook to retrieve data from:
Workbook Name 19 Worksheet Name App - Web Site Data Range for Match F70:F333 Data Range for Retrieval AK70:AK333
Workbook for match & result:
Workbook Name 01 Worksheet Name (ph) drp Range for Match D70:D110

IF 01 (ph) drp D70:D110 matches 19 App - Web Site F70:F333 return AK70:AK333

Any assistance gratefully accepted.

 

[mumps]

Where in (ph) drp do you want to return the data?

 

[ozbeachbum]

Hi,
01 (ph) drp AT70:AT110
Thanks.

 

[mumps]

Make sure that both workbooks are open. Place this macro in the "01" workbook and run it from there.

Sub CompareData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object
    Set desWS = ThisWorkbook.Sheets("(ph) drp")
    Set srcWS = Workbooks("19.xlsx").Sheets("App - Web Site")
    v1 = srcWS.Range("F70:F333").Resize(, 32).Value
    v2 = desWS.Range("D70:D110").Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), v1(i, 32)
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            desWS.Range("AK" & i + 69) = dic(v2(i, 1))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub