VB: transferring data from one workbook into numerous templates -

[jousley]

Hello all. I am trying to assist a Non-profit to be able to get some of their data organized and usable forms developed. I am just about done, and have to get over this very last hurdle. I have attached workbook samples to clarify, as it seems complex to me.
Here goes:
I have a workbook called Data, which contains a worksheet with all of the data that I need to compile. The data includes client names, date of service, and the various categories of services provided and client related info. I have a second workbook called BillTemplate, which is a blank form that I want to transfer the data to. The idea is that the VB would open the Bill Template, copy the right data, save under a unique name, and close - then do again for the next unique client name in the Data workbook.

Copying the Right Data: here's what I am trying to have happen: in Data, under client name, there is a list of names which will repeat. I want to be able to create 1 BillTemplate for each unique client name. in Data, under Date of Service, there is a list of dates which will repeat. I want to list the unique dates under the Date column in the Bill Template for each client, in ascending order.
Then it is a matter of copying the services provided for each unique client, on each unique date to the correct columns: Attendance in Data should go under Attendance in Bill Template(this will be only 1 value per unique date), the "amount" in data relates to the procedure column - that amount should go under the same procedure column in Bill Template. If there are duplicate procedures for a particular date, the amounts should be summed. Lodging, Lab & Drug Testing should be summed under each unique date.
And the last thing is to transfer the funding (always stays the same per client), Counselor (always will be the same) & client ID(the same) values for each client to the Bill Template in the correct cell(each have cell names, funding, counselor & clientID respectively.)

so overall it would be. Open template, copy client specific data, save template with unique name, close, do again for nxt client.

I would greatly appreciate help with this, as it is beyond on my ability at this point. Have been researching pieces of the pie, but can't seem to understand the best way to go about this. I thank you for any support.
J

[Alf]

I do have problem with the layout of your "BillTemplate.xlsm" file as it's so different from how the data is set up in your "data.xlsm" file.

To sort out the data you need, I feel that using auto filer is the best option. So I've set up a model that copies the "Client Names" to a new sheet named "Analyze".
The macro then deletes all the duplicate names and names the rest of the list "ClientN".

Applying autofilter to the client data in the "Data(hidden)" sheet the data for each unique client is filtered out, and this data is coped to a new sheet with client name as tab name.

After looping through all data sheet "Analyze" is deleted and autofilter removed from sheet "Data(hidden)".

This is not what you asked for but much easier to do so perhaps this could be of use to you. I'm also a bit uncertain about using the clients name as criteria for
autofiltering, there may be different individuals with the same name so perhaps using client #ID would better?

To test this setup run macro "ExtrData"

Alf

[jousley]

thanks for the starting point. So if I have each client's info separated into their own tabs, I still need to figure out how to identify duplicates in Cols A, B & D - then if dup, sum the values in Col E in the top most iteration, and delete the other dup rows.
After I figure that out - onto how to transfer the data to the Bill template correctly.

Any additional support is greatly appreciated.
J

[mikerickson]

I've set this up on the assumption that the ClientName is always associated with the same ID number and the same Counselor and the same funding.

I've moved your template sheet into the Data workbook.
You didn't mention where you wanted the individual client bills to be put. This puts it in the Data workbook.

(Both of these can be addressed easily if that doesn't work for you.)

The merged cells in the Template have been replaced with CenterAcrossSelection. (Merged Cells can cause unexpected errors)
The trailing spaces in the Template headers have been removed.
Data validation has been added to the Data sheet, to avoid misspellings (e.g. "treatmetn").

I notice that Dianne was charged for lodging multiple times on the same day, I added some handling to deal with that situation.

I hope this will do what you want.

[Alf]

I still need to figure out how to identify duplicates in Cols A, B & D - then if dup, sum the values in Col E in the top most iteration, and delete the other dup rows.

I'm not sure I do understand you. From the "data.xlsm" I've extracted the information for each unique client name and added this as a new sheet to the "data.xlsm" file. There is also the possibility
to "save" each client sheet as as standalone xlsx file i.e. "Doe, John.xlsx" to a folder of your choice instead of adding it to "data.xlsm" file.

What you wish to do i.e. "identify duplicates in Cols A, B & D - then if dup, sum the values in Col E in the top most iteration, and delete the other dup rows." Could you perhaps upload a file with a sheet showing before and after so I can have a look at it and see what I can do?

From a macro writing point of view the "BillTemplate" is a difficult file to work with at the moment. Could it be rotated 90 degrees perhaps? I guess there is a matter of nice layout and easy readability clashing with macro writing ease but don't worry. If I can't sort this out there are quite a number of clever forum members that are able to do so.

Alf

[jousley]

MikeErickson! This is amazing! Thank you! it looks great. And you put a lot of time into it. I truly appreciate it. Alf, I agree I am using someone else's format. Thank you for your time & effort!

A few items:

For lodging, lab analysis & drug testing for the same client on the same day: can we default to ignore summing - or in other words it should always be 1 max of each per day.
For duplicate "procedures" for the same client on the same day, can we just default to summing them without the message box?
I need to add to copy each bill template to a new workbook, and save as "client name" & "todaysDate" . The file should be saved in the following folder: C:\calohm\"counselorName"\calforms\
This folder structure will already be developed by the time someone is generating these bills. Then after saving, to delete the tab in the original workbook with the client's data.

I want to say again - Thank you! The staff at this non-profit will be super elated that we finally got this down! Much appreciated Mike! This code is so over my head, I'm concerned I won't be able to modify when applying to other programs. But I will figure that out eventually - and am so thankful for this.
J

[mikerickson]

In the ProcessOneDatum sub, there are four blocks of similar code (for ServiceValue, LabValue, DrugValue and LodgingValue)

Please Login or Register  to view this content.

For lodging, lab analysis & drug testing for the same client on the same day: can we default to ignore summing - or in other words it should always be 1 max of each per day.

To create a maximum of 1 per day, change the LabValue, DrugValue and LodgingValue sections to look like

Please Login or Register  to view this content.

For duplicate "procedures" for the same client on the same day, can we just default to summing them without the message box?

Please Login or Register  to view this content.

I included the MsgBox mostly to handle data entry errors. These automatic routines (automatic max 1 or automatic sum) do not alert the user to duplicate entries.

[mikerickson]

About modifying it. I suspect (mostly from your Named Ranges that refer to a Mt.Carmel workbook) that you might have different templates for different hospitals.

That can be handled by passing the template that you want, as a worksheet object, as the FromTemplate argument of the ProcessOneDatum sub.
Similarly, if you want the resulting bill to be in a different worksheet than Data, then pass that workbook to the InWorkbook argument.

Note that all these workbooks must be opened before calling the ProcessOneDatum sub.

Please Login or Register  to view this content.

[jousley]

I was able to modify some of the code as mentioned above. I have one last issue, which I think you tried to explain to me, however I am not sure how to proceed and finish his project.
When each new worksheet is created using the Bill Template for each client, I would like to:
1) copy that sheet to a new workbook
2) Save the new workbook with clientname & todays date in a predefined folder
3) Close the new workbook
4) delete the client's worksheet in the Data
and then go onto the next client's worksheet.

I can't seem to identify where to add this code. I tried to locate in loops, but am not sure. Would you be able to assist me on where to add this? It would help me finish this thing.
Thanks again for all your time & assistance.
J

[mikerickson]

I'll look at this later, but different workbooks holding different bills. ????

It sounds like the easiest thing to do would be to after the existing code has run. (and "sort each bill by date" has been added).

Then take those newly created sheets in one workbook and split them into different workbooks and then delete the intermediate sheets.

One thing that I was concerned about (and the message boxes addressed) was if someone made a bill, then added proceedures to the master list (before deleting the already billed data) and billed again, double billing for the previously billed procedures.

[jousley]

Thank you.
Each staff member will be entering services for different clients each week, which is collected on the Data Tab. At the end of the week, they will "generate bills". The bills should be by client, each in their work workbook so managers can easily find a client's bill for a particular week.
After I "generate bills" I will put code in to clear contents of the Data tab so no double billing will occur.

I am working on some VB that will select the names of the client worksheets, copy, save as, etc. just running into some testing issues. Hope that clarifies.
And yes - you are right - I need to figure out sorting each bill by date..

[jousley]

Mike, I figured a workaround which copies each worksheet into a workbook & saves with a custom name. Then ran a loop to delete all newly-created tabs. It's probably not the best coding - but it does work for now.
Your help is much appreciated.
J