Cope data from another sheet based on cell criteria

Sheet 1 contains some person date (name, address etc) and a study they are involved in is in row C.

First Name Surname Study
Stuart Jones X
Jess Jones Y

I have 2 sheets named 'x' and 'y'

I have this code which i can put into sheet 'x' and it will pull all the information from my data sheet for anyone who has 'x' in row C. In this case, Stuart Jones.

I can also put the code into sheet 'Y' and it will do the same thing - no problems there. However i would like to modify this code to be able to run it on all sheets at the same time; the trick being the sheet value MUST be the same as the sheet name. My code only works on active worksheets, which isn't ideal.

Can anyone help me?

Please Login or Register  to view this content.

Many thanks
Stuart

Hi Sintek,
It seems to do something but it's not pulling the data - oddly it seems to randomly colour fill some cells in the sheets that have data to pull.

I know i said the range column would be C but that's changed to H so i made some changes - maybe i've missed something?

Please Login or Register  to view this content.

Actually, scrap my last comment. I tried it again and it worked perfectly!!

Thanks for the help!

Stuart

I may have jumped the gun a little. It's not quite working every time. If i run it from my 'home' tab it doesn't work (i get that formatting issue) but if i run it from a study tab it works fine.

I don't seem to be able to upload my file...
Untitled.png

but i can upload it to google drive: https://drive.google.com/open?id=0B6...1JBTU9PYldsdE0

Let me know if you have any questions

Stuart

Thank you - Here's my attachment

I will either create a button on the home page or have it run when i add a new patient.
So the active sheet will be the 'Home' sheet - i need it to run on all the other sheets without having to go into each sheet.

Okay, so when i use my add patient form from the home screen it will add the patient details to the 'patientlist' sheet, including a Study id which is populated in column H of 'patientlist'.

Now, each of the study sheets, such as 'annexa4' needs to stay updated with all patients that are registered on this study - it will pull all rows where 'annexa4' is populated in column H.

This needs to happen on every sheet. The original code works but i manually have to go to each sheet and run the code - i would like to update all sheets from a button on the home tab.

I'm sorry if it's difficult to understand!

I can't thank you enough! That works just how i need it to.
Just one niggle... After I click the Thank you it now redirects to the PatientList sheet but i would rather it stays on on the Home sheet. What do I need to change for that?

Thanks again
Stuart

Al, it seems to duplicate when i add another patient to the list. Is there a way to prevent that?

I'm so sorry, i've also noticed another requirement i've not thought of. I know i'm being a pain!
Is it possible to only copy and paste columns A:H? It's just further columns will be active in the study sheets where users can add further data.
As it is, copying and pasting the entire row will delete any data added in columns I onwards.

Thanks again sintek, this has worked.
The only issue i'm now left with is that where someone adds includes a date in the userform it's not recognised as a date unless i 'text to columns' the row (row H in this case).

Is it possible to add this onto the end of your code? Then it will do everything i need it to!