Search worksheet in another workbook and pull matching Data

Hi all,

I am trying to code a function into a userform whereby when someone enters an account number (accountnumber_Change()) it searches a worksheet for that account number and if found pulls the order number requirement (which is in the cell next to it) from that worksheet and displays it in the userform.

I have code that is working but it searches a worksheet in the same workbook as the userform. I am wanting to change it so it searches a worksheet in another workbook.

The second workbook I have is called 'Authorisation_Log.xlsx' and the worksheet is called 'Order Format'

Any help is greatly appreciated.

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Hi John,

Thanks for your help.

It was nearly right for me, had to change a few things,

I had to add .Open to the 'Set ws = Workbooks.Open("...\Authorisation_Log.xlsx").Sheets("Order Format")' and had to add 'End With'.

ended up with:

Please Login or Register  to view this content.

How can I get it to search this work book without it opening?