Search for a value in a workbook, and return the Spreadsheet name where it was located.

Hi guys, I am having difficulty with a function for a program I am building. Some background to the program - It has one workbook, with multiple spreadsheets (These are hidden from the user). The spreadsheets are named after each department, e.g "Production Office", "QA Office", "QP Office". In each of these spreadsheets there are 2 columns of data: 'Batch Number', 'Reviewer'.

What I want to do:
A search function, where on the appropriate userform, the user will enter the Batch Number they are searching for, e.g "1E2345" and click the search button. The program should search for the number throughout the multiple spreadsheets in the workbook, and return the name of the SPREADSHEET where it was found and return the name of the Reviewer (which will be the cell directly to the right of the cell that was found in the search).

At the moment all I can do is find the cell I am searching for, but can't return the Speadsheet name and Reviewer name.

My original intention was to return the Spreadsheet name and Reviewer name in two separate Text boxes on the Userform. But if this is too difficult to achieve I would be happy to return the search details in a Message box.

Many thanks in advance to anyone who has a look at my problem.

An example of what the userform looks like :

br search for forum.PNG

Hi Leith, Thanks very much that code done the trick for me. I am about to post up another query about creating an "event log" on a user form, if you wish to help.

Thanks again!