Hi,

i am having trouble adjusting the below code in order for the autofilter to work from another closed workbook. It works fine when it is used on the same workbook and same sheet, but I don't know how to alter it to search another workbook...

In short, what i am trying to accomplish:

in current workbook, click in a cell (sheet1, cell A5) - Now look for that value in a separate closed workbook, on sheet 1 column A
autofilter all the records that match to the value of cell A5 from the original sheet...
display that result in a userfom listbox in my original sheet...
the second workbook is used as reference only and if it is opened, it should be closed and unsaved before the userform is displayed..

any help would be appreciated.

thanks.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    If Target.Column > 2 Then Exit Sub 
    With Cells(1).CurrentRegion 
        .AutoFilter Target.Column, Target.Value 
    End With 
    UserForm1.Show 
End Sub

Private Sub UserForm_Initialize() 
    Dim a As Range, n As Long, Val As Variant 
    Set a = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(12) 
    With Me.ListBox1 
        .ColumnCount = 3 
        For Each Val In a 
            .AddItem 
            .List(n, 0) = Val: .List(n, 1) = Val.Offset(0, 1): .List(n, 2) = Val.Offset(0, 5) 
            n = n + 1 
        Next 
    End With 
    ActiveSheet.AutoFilterMode = False 
End Sub