Copy info from 1 sheet to another if data is in cell A, if not then use cell B

[theparlourroom]

I'm completely new to VBA, so I hope this makes sense! I have a master page with all my data on it called "client list". I want to extract data from this master list and move it to other sheets. I want to run a macro that says "if a line of data has Sally's name under the 'allocation' column (column M), and there is no 'discharge date' (column V), then copy to the sheet called Sally, but if there's no name in the 'allocation' column, then use the data in the 'assessment' column (column H), and if the name in the assessment column in 'sally', then copy to the sheet called Sally". Hope this makes sense!!!! So far I have the below, but it's not working

Please Login or Register  to view this content.

[Richard Buttrey]

Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[theparlourroom]

Apologies! I hope it is right now.

[Richard Buttrey]

Hi,

Yes that's fine. Thanks for that.

The first question I'd ask is what's the essential requirement?
Is it to be able to see the records just for a single person, e.g. Sally, at any one time. i.e. do you really NEED separate sheets for individuals. Or would a single output sheet which would be updated in response to selecting an individual's name from a drop down list be sufficient? My general approach with these sorts of things is to avoid clutter and keep things simple and elegant.

Even if you need to see lots of individuals' details have you considered a Pivot Table? This would avoid separate sheets for each individual - which I instinctively feel is not the best way to handle this.

Upload your workbook and let us have comments on the above. Your current approach is using a loop, and whilst loops have a use in some situations this is not one of them when a Data Filter macro is much quicker and simpler.

[theparlourroom]

Thanks so much for your reply Richard. I will upload a copy of the spreadsheet when I'm back in the office on Wednesday.

No, doing it this way certainly isn't an essential requirement. Anything simpler would be a blessing! But I'm currently "below novice" status, so not sure what my options are.

I manage a mental health facility. What I have is a list of patients, with details including which therapy program they're in, who is assessing them, and who is doing continual therapy with them. Once their file is closed I also put in a closure date.
I sit down with my staff and go through their patient list every fortnight, so I need a list of the patients they see (as opposed to one huge list that includes all patients assigned to all staff). The list will include patients they're assessing, as well as patients they are seeing for continual therapy.
I know how to use code to make a separate tab for each of these 2 categories (1. Clients Sally is assessing that aren't closed, and 2. Clients Sally sees continually that aren't closed), but not one tab that combines both.

Please Login or Register  to view this content.

Things are complicated because:
* the person who assessed a patient may not be the same person who sees them on an ongoing basis
* an individual may still be in the assessment phase and not yet assigned an ongoing clinician
* the clinician seeing the patient can change at any time (although not often), so I need the data to be reanalysed when needed.

I hope this helps clarify the situation. As I said, in 2 days I'll upload the spreadsheet (although I'll need to remove all identifying personal information for patients first!). Anything simple is vey, very welcome. Many thanks for taking the time to help me.

[Richard Buttrey]

OK, Let's see what the workbook shows on Wednesday.

In the end this will boil down to creating a regular 'normalised' database. i.e. Unique fields of data across the columns and individual patient records down the rows. Once that's in place then just about anything is possible. It may well be the case that your master client list is already acceptable as a database, and if not almost certainly it can be quickly converted to a usable database.

[theparlourroom]

I have attached my excel spreadsheet. I appreciate any help.