VBA? to search worksheets and return contents of cell

[andyroe208]

A long title I know!

We currently use excel 2007 to keep record of many different makes and models of laptop recovery discs and the location of these discs.

I have been looking to make it as simple as possible for everyone to use by incorporating a search function (maybe a popup box) that will ask for the model number and return the location of the disc.

We currently deal with 13 different makes so have 13 different worksheets. each sheet has 5 colums that are used the first for part number second for model number, third for location, fourth for quantity and fifth for date added.

Can anyone help me in creating a method of searching all worksheets on column 2 and returning the contents of column 4 for the respective model?

if any further information is needed please feel free to ask.

Many thanks in advance

 

[VoG]

Try this http://www.ozgrid.com/VBA/VlookupAllSheets.htm

 

[andyroe208]

Thank you, that has given me a good starting point but how would I go about getting the the location in our warehouse to show in a popup box?

Or does this do that an I am being really thick?

 

[VoG]

Do you mean that you want to return the name of the sheet where the match was found>

 

[andyroe208]

OK so maybe I didn't explain as well as I could have done.

I would like to search 13 worksheets for a model number, usually we search on column B for model number then we look in colum D for the location in our warehouse. We have 13 different worksheets so would need to search all of them then return the value of colum D for that particular model.

 

[VoG]

Does the VlookupAll return the first match? If so it could be modified to return all matches from different sheets.

 

[andyroe208]

I will have to test this code and see how far it would get me but I feel it would search and return the value of the search string instead of the contents of the cell 2 to the right of what we actually ask it to look for