Pause macro for user input then search for user's answer across multiple sheets

I have a workbook that has multiple sheets with one sheet for each employee.

I need the macro to pause for the user to enter an employee's last name then search all sheets in the workbook and return specific information about that employee from the sheet where the name was found.

I know this would have been better as a pivot table or some kind of database, but since the information is already there I'm just trying to get what I need from what I already have.

I did post this question on another site a few minutes ago, this is the link: "http://forums.techguy.org/software-development/873858-excel-vba-code-question.html#post7011036", but then I found this site and began searching and I found answers that have gotten me so close to what I'm looking for that I thought I would give it a try.

Any help you can give will be much appreciated.

Thanks

Wow, I love that formula and I will use it, but for this application I would like to have something that I can use without having to update the drop down box when there are additions or deletions from the names that would have to appear there.

Maybe I'm not fully understanding, but I think with a drop down box I would have to update the names each time I have a new one or loose one. I'm also wanting to use the search macro as a small part of a larger macro. Not even sure if that is possible, but thanks for your help.

Ok, I am attaching 2 Excel workbooks. But please understand that I have only started writing the macro and got stuck on this part. I appreciate your help, but I'm really not asking you to do all my work for me, just help me get past the part I'm stuck on, but I'll have to explain everything I'm trying to do for it to make sense.

Ok, here goes I'll try to explain it all. One workbook is the one with multiple sheets that I'm trying to search through. It is the accounts receivable record for customers. Sales persons enter weekly sales amounts to each of their customer records.

The second workbook is a receipt that is completed when customer payments are received. There is a button on the first sheet of that workbook that I have used to assign the macro I am working on.

The idea is this: the accounting clerk will open the receipt document, click the button to start the macro. It will pause for the user to input the date which will be entered into the appropriate cell on the receipt. It will then open the accounts receivable document and pause for the user to input the customer's last name and search through all sheets for that name. That is the part I'm stuck on and as far as I am in writing the macro. If I can get past this part I can do the rest. Now you will probably read the rest of this and tell me that this is not the most efficient way to go about this and as I said I appreciate any input that will make my job easier, but please don't think I'm asking you to write the rest of my macro.

What will happen once it finds the customer's sheet is copy the date previously entered to the next available row in the "date" column and the receipt number from the receipt to the next available row in the "Rec #" column in the accounts receivable document.

It will then copy the customer name, salesman's name, and account balance due from the accounts receivable document to the appropriate cells on the receipt document.

The macro will then pause again for the user to input the payment amount received from the customer, which will be entered into the appropriate cell on the receipt document as well as copied to the next available row in the "$ Recd" column of the accounts receivable document.

The macro will then pause again for the user to select the payment method (cash, check, or money order) from a drop down box in cell "E11" on the receipt document. If the user chooses check or money order they will then enter the check or money order number in cell "F11". Both of these will then be copied to the appropriate cells in the accounts receivable document.

The final thing is that the receipt number is pulling from the sheet named "DATA" so the value in cell "A3"will be copied to cell "A1" on that sheet so that the next time that document is opened the receipt number will have advanced by 1.

I hope I have explained everything. Please let me know if there is something else you need to know.

Again, thanks so much for your help.

I'm not sure I understand what you are saying. I know how I could have a database that has each sales person and each of their customers, along with the address, phone number those kind of things, but how could I have the ongoing balance due without having a separate sheet for each customer? I also need to print a receipt of the payments that goes back to the customer.

You may be right, but I just don't understand.

Thanks,

Sassy 2