I have no idea if Excel is even the correct program to be using, but it is what we are currently using at my company, and I figure baby-steps are the best way to get anything accomplished.

I work for a metal recycling company, currently we are using paperwork that is so outdated, saying that it is stone-aged may be putting it mildly… (just switched away from carbon-paper, to carbon-less paper about a year ago). Anyways… we ship barge loads of metal, but due to our location, we cannot load directly into the barge, so we have to ship, by truck, about a mile down the road to our dock. Since each truck is going over the road, it needs to be loaded legally, and have the appropriate paperwork necessary (a Bill of Lading).

Currently, when we ship a barge, we have to type via a typewriter, all of the consignment information, addresses, sale orders, etc. onto a preprinted 6 page carbonless BOL (preprinted with the shipping disclaimers, signature lines, etc). This process is time consuming (even running a macro on the typewriter, it still takes over a minute per BOL) so it needs to be done ahead of time, we usually start a day or two early, when we have a free minute or two in between our other shipments and deliveries. Then, once we have a stack of BOL’s printed for that barge (about 90% of how many we think we need, because we cannot print extras) and we begin loading, we still have additional information to add to each BOL when the truck is finished loading; the truck number (we always use the same carrier), and the gross, tare and net weights.

We also have to keep a spreadsheet of the weights so that we have an idea of how far along we are with loading. Currently, we use Excel for this; it keeps a running tally of our weights, and calculates average weights, an estimate of how many loads remain based on the target weight, etc.

Since we are already entering this data into a spreadsheet, and the BOL’s we use are sequential, and only used for the barge, I figure there has to be a way to cut-out the preprinted carbon-less copies, cut-out using the typewriter, and speed everything up overall.

For example, our spreadsheet is setup like this:
Date BOL # Truck # Gross Wt. Tare Wt. Net Wt.
4/10/12 N1099 203 76,260 41,020 35,240
4/10/12 N1100 B-2 80,000 35,120 44,880
4/10/12 N1101 49 79,960 36,820 43,140
4/10/12 N1102 53 79,820 37,680 42,140
... ... ... ... ... ...
4/11/12 Complete 68 Loads 5,612,860 2,570,300 3,042,560

As I said, I do not know if Excel is what I should use, but it is what we are currently using.

What I would like to happen: is every time we enter a completed line on the sheet, to have Excel, or a script, create a new Sheet (basically, create the BOL for that truck from a template that I have not made yet), auto-number it based on the "BOL number", populate it with the Truck number, gross, tare and net weights, and have it auto-print multiple copies (for the driver, for our accountants, etc). I’m open to any and all suggestions, even if they are outside the scope of Excel. (Although, I would like to stay within the Office suite, since we already have it installed, or some sort of free software, since I do not have purchasing authority for my company.)

Thank you all for your help!