Finding like data in other workbook

[jddl]

In one workbook i have a list of customers. In another I have a list using the same customer names in one column and a date assigned to them in another column. I would like to run a macro that when a customer in the first workbook is selected it would find the same customer in the second workbook and return the date associated with that customer.

 

[NotAGuru]

jddl:

You need to use the Workbook_SheetSelectionChange event. With the workbook "Book1" active (the one that only contains the customer names), press Alt+F11 to open the VBA Project window. Find the reference to VBAProject (Book1). Double-click on the ThisWorkbook icon. In the first box above the code window, select Workbook. In the second box, select SheetSelectionChange. Enter the following code:

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim strSearchInfo As String
Dim strFoundInfo As Date

strSearchInfo = ActiveCell.Value
    
Application.ScreenUpdating = False

    If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
        Workbooks("Book2").Activate
        Cells.Find(What:=strSearchInfo, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:=False) _
                .Activate
        strFoundInfo = ActiveCell.Offset(0, 1).Value
        Workbooks("Book1").Activate
        Range(Target.Cells.Address).Offset(0, 1).Value = strFoundInfo
    End If

Application.ScreenUpdating = True

End Sub
/[code]

When you select any cell in Book1, the date from the corresponding cell in Book2 is copied to the adjacent cell in column B.

Rick

 

[jddl]

Thanks for the response works great. Might be a stupid question but is there any way to get the date without having Book 2 open?

 

[NotAGuru]

jddl:

My understanding from other posts here is that it might be possible, but it is far easier to just open the other workbook. To make this painless, add the following code in the same place you entered the other code. This will automatically open Book2 when Book1 is opened and close it when Book1 is closed. It is assumed that both workbooks are in the same directory path. If they are not, you'll have to include the full path to Book2 where referenced in the Workbook_Open event code.

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Workbooks("Book2.xlsx").Close SaveChanges:=False
End Sub

Private Sub Workbook_Open()
    Workbooks.Open ("Book2.xlsx")
    Workbooks("Book1.xlsm").Activate
End Sub

If you want to be able to save changes made in Book2, set the "SaveChanges" parameter to True.

Rick