Modify existing VBA coding to expand data retrieval

Sorry for the long winded explanation but to avoid questions later I thought it best to be specific.

I have uploaded two files - a manifest and a driver log. I transport children to and from the Richardson Center and each driver is required to fill out a driver log to turn in to the state for each day (one in the morning and one in the afternoon). The manifest is given to us by the state with each child to be picked up for that day for 4 separate routes (4 vans).

Between myself and the generosity of others, I have managed to create an excel program that auto grabs the information out of the manifest file and populates the driver log. The information populated is the name of the child and the child id number which changes each morning and afternoon.

My company liked the project so much they wanted me to make a version for them to sort out all 4 routes instead of just my own. I am not sure how to modify the VBA code to do this, though I suspect it would be relatively easy for someone more knowledgeable about VBA.

Once you open the notebook you will see exactly what it is I am trying to do. In column A is the ID number and column B is a list of names that the VBA should look for in the manifest file to store in Columns O:R. Column L sorts the names out and retrieves the ID number to be inserted in column A (due to the names on the manifest not matching the way they should be written out this column is necessary, ex: manifest says: "Smith, Richard, Joh" which on the driver log should be listed as "John R Smith"). I have created 10 tabs for 10 possible routes. Each tab, when refreshed is clicked, just needs to populate the names in the B column with the appropriate codes from the manifest.

Any help would be greatly appreciated.

Ahh,

I was just going to ask about that........

OK, Looking at the two files I can't find any of the IDs listed in column A of Route1 in the daily file.

Furthermore why do all the routes show the same IDs? Have you just copied Route1 an din reality they would all contain different sets of data.

Can we rewind to the essential task here. It seems to me that you have a daily log of pickups required, all of which need to be allocated to a route, and each route is allocated to a specific driver.

There are 80 pickups to be allocated to 10 routes which implies an average of 8 IDs for each route. What determines which ID is allocated to which route and how do you ensure that each pickup added after the first can actually be accommodated and the driver has finished the previous pickup? There doesn't seem any logic in your existing macro that would address this particular constraint?

I think we need to understand precisely how you see this allocation of 80 pickups to 10 driver/routes working.

The Booking ID column on the Manifest (the file name with a date) has the ID numbers. The tabs show the same id because I just copied and pasted the first tab to the other 9. When the names are changed in column B by hand the ID number should refresh automatically when the button is clicked.

Certain kids will always stay on a certain route/van/driver. They cannot change back and forth. Some drivers may have 8, some 10, some 12 so it cannot be divided like you say. Right now there are only 4 routes. The additional 6 routes (tabs) is for a possible future development.

In a nutshell, I need the program to look into the manifest, look at the names in column B in the driver log and find those names in the manifest to store in the colored table along with the associated ID numbers. The ID numbers are different for morning for a kid then from the afternoon. Each route on each tab will follow the same protocol when the refresh button is selected for the names on its own sheet column B. Does that make sense? As of right now, the current code is set to look at the date (on cell F3 in the driver log) and look for a filename that is associated with that date (in the format 00-00-00). So each new manifest that comes out daily will be labeled with a date and when the refresh button is selected it will search for the new date, daily.