Copy rows to another sheet if any cell in the row contains a certain word

[theavkid]

Hi there, I'm new to Macros/VBA and to this forum too so I would be grateful if people could use language that I would understand!

I have a database of about 6000 patients with their details (name, DOB etc.) and their diagnoses (Addisons, Turners etc,). These diagnoses come under the umbrella of the organ they are associated with. For example, Addisons is a problem with the adrenal gland and Turners is a problem with the ovaries. Some patients have multiple diagnoses with one organ, or even multiple diagnoses with multiple organs.

My job is to create new sheets from this 'master sheet' containing only patients with adrenal problems in one sheet and only patients with ovary problems in another and so on and so forth for each organ. It is also important that patients who have problems in multiple organs appear in each respective sheet. E.g. a person with adrenal issues appears in the adrenal sheet and if they have ovary issues they also appear in the ovary sheet.

I've attached an excerpt from the database and for obvious reasons I have replaced the confidential information. If you notice, not all of the columns are full, which I hope ought not to be an issue and also some patients which are a work in progress don't have a diagnosis and as such they are labelled with an 'X' in the first diagnosis column. Moreover, some patients will not have a diagnosis and will be labelled with the tags UNDIAGNOSED and/or DISCHARGED.

Basically I need to copy whole rows containing the word 'ADRENAL' into the sheet labelled 'Adrenal.' And again with the other organs as well. It's important that the whole row containing all of the patient information is copied along with the diagnoses.

I imagine that I would need a macro for each organ....but I literally don't know anything about macros/VBA so I shall stop guessing!

I have attempted to read other threads but I find it all very confusing, hopefully by talking about my example specifically I will get it.

Sorry for the long message but I wanted to be thorough and hopefully I have been. Reply if any other information is required. I will endeavour to reply ASAP but as I'm sure you can imagine, doctors don't get a lot of free time!

Kind Regards

P.S. I forgot to mention that ideally the codes would also work for future data being entered as I intend on expanding the database.
Template Original.xlsx

[fredlo2010]

Hello Thea,

See if this works?

First you need a macro that will filter the organs and copy them to its respective sheets.

Please Login or Register  to view this content.

and then a code to go through all the sheets pasting what was filtered.

Please Login or Register  to view this content.

The only code you have to run is the second one. Put them both in a same module.

I have attached a file for you to see. Attachment 262413

[theavkid]

Thanks for the reply fredlo2010!

It works perfectly! You are a godsend, haha!

Appreciate it so much, this is going to save so much time!

[theavkid]

Hello Thea,

See if this works?

First you need a macro that will filter the organs and copy them to its respective sheets.

Please Login or Register  to view this content.

and then a code to go through all the sheets pasting what was filtered.

Please Login or Register  to view this content.

The only code you have to run is the second one. Put them both in a same module.

I have attached a file for you to see. Attachment 262413

I have one query though. I have noticed that some people with Thyroid problems in the master sheet don't appear in the Thyroid specific sheet. I am not sure if this is because the word 'Thyroid' doesn't appear in the first Diagnostics column (D1). Is there a way of getting patients with umbrella labels like that no matter what column they appear in, to appear in the respective organ sheet.

Thank you!

[fredlo2010]

you are right let me see how I can solve this .

[fredlo2010]

Thea,

I think we got it this time.

this is the function

Please Login or Register  to view this content.

and the main code

Please Login or Register  to view this content.

Thanks

[theavkid]

Thanks so much! It works really well. I appreciate it so much. I don't understand the code at all but so long as it works. If I had more time, I'd love to be able to learn more about it as I'm sure it would save me so much time in all of my excel projects!

Thanks again

[fredlo2010]

I am glad it helped you