I need a macro that will pull out a patient's details from different sheet

[emymeeky]

Hello guys. I have a worksheet with details of patients in different sheets. There are sheets44 like malaria, diabetes, HIV,admissions where details of patients (like name and date) who suffer from the sicknesses are stored. A patient can suffer from more than one sickness. Each patient has a unique file number. I need a macro that will fish out the patient's details from each of the sheets, stating the sheet where each file number were found and the date recorded against the patient in each of the sheets. for example, A file number 12345 belonging to a patient, Mathew Greg was found in sheets: malaria and diabetes. When I search for 12345, I want the following result to be displayed.

file number: 12345
Malaria: 3/7/2015 (as recorded against his name in the sheet)
Diabetes: 4/7/2015 (as recorded against his name in the sheet)
These information may be displayed in a userform or in any convenient style

Is it possible that these information be transferred to another sheet named "folder" ? when a button is clicked?
Attached is the workbook

[Richard Buttrey]

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the forms/sheets that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

I would urge you to combine all your patients records into a single straightforward two dimensional table. This will no doubt include all your existing column labels (and I'm assuming all patients' sheets are the same layout) with an additional column for file number. Each row would be a unique piece of information.

Once you have that your requirement is simplified immensely.