Though a pivot table may seem great, and yeah perhaps all the data should be grouped together on a single worksheet, the overall process is not broken to the point of needing a complete revamp. Besides each of the other two fixes would require writing more code and performing more searches and still be limited, and ultimately not the way that the the customer is looking for. And actually, it almost sounds like this should all be done in Access rather than Excel, but once the company has committed to a particular means of working, it can be difficult to change it unless required by someone of higher authority/there's a big problem.

Here is an idea that can be done keeping your existing formatting.

If the "total" that you are looking for is always in the same position in relation to the numbers being totalled then you can write some VB code to sum up the items in your "group"

As I understand you have groups of data like the following:

Company1 Item1 $4.51
Company1 Item2 $5.23
Company1 Item3 $20.63

Company2 Item1 $6.52
Company2 Item2 $8.02
Company2 Item3 $10.41
Company2 Item4 $11.06

And say you want to add the "Total" just below each of the company groupings, then you know that all items to be totaled are located "above" the cell used to hold the total. The total cell could be anywhere in the row, but as long as you (the Programmer) knows where the last piece of data to be summed starts, then you can do the necessary addition.

The routine you would want would simply start at the last item to be summed, and work "up" until a blank space is found. The result then is the sum of all items for that group.