Copy Data Range to another sheet based on a condition.

Ok so I am a new member to this forum and I have been reading post after post regarding macros and formulas and I am not getting anywhere.

Here is the problem:

I'm sure you all have heard about the medicare penalties regarding hospital readmissions. I have a spread sheet that we log patients into if they have been readmitted to our facility in less then 30 days since their previous discharge. I know that this would be better suited in access but I only have excell to work with d/t departmental butget. Our departmenet has 14 case managers and we are all entering data into this sheet.

What i need is:
1) A way to make all enteries in caps whether it is entered in caps or upper and lower
2) Based on the colum CHF readmssion (yes/no) = the answer yes, I need the colums from sheet 1: "r,q,c,d,f,g,h" of that patient copied
to sheet 2 for a follow-up call once a d/c date in collum R is entered in sheet 1. of course this data will need pasted in the next
available row on sheet 2 from sheet 1.

The problem that i have now is i am using a copy and past formula based on a yes value in the chf readmssion collum, but this does not allow me to sort data an is also leaving lines inbetween data sets. This is hard for everyone to work with.
I will attach a blank copy of my sheet hopfully somebody can help me.

Thanks
Mike

can not see "Based on the colum CHF readmssion (yes/no) ". You do not have data in column CHF in any of your sheets

Collum "P" is a drop down list which offers "yes or no" i had to take out my data due to HIPPA.

Okay! If column P ="YES", you want to copy "r,q,c,d,f,g,h" columns of sheet1(Jan) to sheet 2(Feb). Is this right? Am I missing some thing?

If column P ="YES", copy "r,q,c,d,f,g,h" columns of sheet1(Jan) to sheet3(Follow Up Calls).

JAN sheet ONLY to Follow Up Calls sheet?

yes that is correct disregard the other months.

Ok, So I pasted this code into January sheet.
I copied some data back into the sheet which included multiplte patients that had a "yes" in collum P but it conly copies the first record then stops. Did i put the code in the right sheet? Did I forget to do something?

Please put the code in to standard module, not in any sheet

I keep getting a Run-time error '1004'
Method 'PasteSpecial' of object '_Worksheet' failed

What does this mean?

Please find attached. I have to remove some sheets as it exceeds the down load limit

THAT IS GREAT IT IS ALMOST PERFECT!!! I HAVE IT APPLIED TO MY SHEET, MY LAST QUESTION IS,,, HOW DO I GET THAT MACRO TO KEEP CHECKING THE "JAN" OVER AND OVER AGAIN? TT MOVES THE DATA RIGHT THAT IS THERE BUT WHEN NEW DATA IS ADDED I HAVE TO RESTART THE MACRO. i KNOW IM A PAIN IN THE ***. BUT YOU HAVE BEEN SO MUCH HELP, I HAVE BEEN WORKING ON THIS FOR WEEKS...

This code clears existing data and repopulate the follow up sheet with the new data, so you can amend the Jan sheet as many as you wish and all you have to do is just re-run the code. The only glitch my this code is, I am unable to create a sheet name with "FOLLOW UP CALLS". The code does not like it, as I compromise you need to slighly change the name of the follow up the same as this one FOLLOWUPCALLS. No space please. What the code does, it check if the followup sheet exists, if it does, it clears the existing data except the header and repopolate with the new data. When you run first time, if the follow up sheet name is wrong, every thing is going wrong. So,please just copy the follow up sheet name from this code and copy it on the sheet name. If every thing is fine, the code will not create the follow sheet name as it is exists.

Please Login or Register  to view this content.

It works perfect thanks!

Ok i just found that when it checks the columns for data that it is clearing too far over to the right other wise it works great. Can the code that clears, only clear from A-G in "the followupcall" sheet? Otherwise it works great!

Please Login or Register  to view this content.

Change this line of the above code

FROM

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

Perfect Thank you for all your help our new sheet is up and running!!!
Thanks again!