Macro to Pull Selective Data

[Swordfish1989]

Hello!

New to this forum but certainly not new to excel. I have a task that I think would be good for a Macro but I'm not sure if it's even possible - much less how to do it. Here's what I have:

We have multiple workbooks, one for each deal we do. Each deal has several clients, which are separated into different worksheets. For example, I have workbooks that say House #1, House #2, etc, and within each workbook I have Clients like John Smith, Sally Jones, etc which are the name of the worksheets. John Smith's worksheet has all his information, Sally's has hers.

I want to create a macro in a new workbook that when I enter a client's name will fill in the other information based on the workbooks where the client is listed. So essentially, I enter in the client's name and the macro would go through the selected workbooks and pull out only information relevant to that person (amount of money paid, interest rate earned, etc). Is this something a macro would be able to do? IF yes, how can I get started?

Thank you!

[xLJer]

The attached files were tested in Excel 2003. You may be able
to use some of the methods in your application. The code behind
the form opens specific workbooks, where the file name starts
with "House", gets all the data and copies back into a "Workspace"
sheet, in the workbook where the code and form reside. A combo box
is populated with a list of real estate properties, and when you
choose a property, another combo is filled with associated
client names. Click OK and the data for the specific property
and client is copied to the main worksheet from the Workspace sheet
and the form closes.

I hope this runs in your version of Excel, and it may not
apply to your application, but could be useful in learning
how workbooks can be opened and data retrieved in VBA code.
I am posting just the form Activate event code. If you decide
to test it, copy all three files into the same folder and open
Master Template.xls.

Please Login or Register  to view this content.