Annoying Macro - Sorting Cells and Summing Totals

[ahilty]

BUYSHEET TEST.xlsm
EDIT: Use this sample workbook to see what I would like the final product to look like




I have an extremely annoying macro that I am trying to fix (it was written in 2001).

What happens is that it organizes the rows by alphabetical order (according to the value in column A). For example: all "Bolts" will be grouped and their associated costs with them. Below that will be 'Nuts' and below that 'Washers'.
Then it is supposed to insert a row and sum the total of that item. For example: 8 rows of bolts with their item costs a row of the total costs of those rows, then 4 rows of nuts with their item costs and a rows of the total....... so on and so forth.

The code that was written uses the following syntax and I believe there has to be a much easier way (with a lot less selecting) to write it.
This also hasn't worked flawlessly. There are several times that the sum formulas don't grab all of the values needed and also sometimes grabb too many values (the rows of the other items).

I know the macro is lengthy but it is repetitive so there has to be a better way. Any ideas?

[ahilty]

I have attached a test workbook with values entered and the macro as is attached.

You can see it doesn't sort all of the bolts into one group nor all or the nuts or washers.

BUYSHEET TEST.xlsm

I know this is a lot of work, so thank you to anyone giving it a try.

[Mumps1]

The following assumes that row 1 has the column headers: "Item" in A1 and "Quantity" in B1. It will sort according to "Item" and give you the subtotals and grand total for "Quantity". You may have to change the headers and sheet name to suit your needs.

Sub SubTotal()
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:B" & bottomA).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A" & bottomA) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:B" & bottomA)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.SubTotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

[ahilty]

Mumps,
This might work for the sorting (I'm trying it now). Please see the attached file to see the full annoyance.

[Mumps1]

Try the attached file. I've had to remove a few rows at the top to make the subtotals work. I hope that's OK. Just click the "Sort and Subtotal" button.

[ahilty]

Mumps,
I like what you did there - I will be inputting this into my code and apply the sum to all fields and see how it goes after I get back from lunch.
Thank you for the help, I'll let you know how it turns out. You've been more than helpful.

[ahilty]

Mumps and others,

I used the above suggested code and I seem to be running into some problems when trying to insert it into what I have.

The rows you deleted to make the header obvious cannot be deleted. Also, the fields that need subtotals under them are the following fields:
(1)Labor Total, (2)Material Total, (3)Sub Total, (4) Owned Equip Total, and (5)Rent Equip Total.

You have reduced the annoy repetition and shortened the code by a mile but it isn't quite doing everything it needs to.
Any other ideas?

PS. The test file is attached above if anyone else would like to try and help.

[ahilty]

BUYSHEET TEST.xlsm
EDIT :This is the same attachment as in the first post... I attached it before I realized I could edit my first post to include it...

I have attached the a workbook that contains the before and after sheets, as well as an existing macro.

Please use this when making suggestions.

[Mumps1]

I'm a little confused as to what you want to do. "Before Sheet1" and "After Sheet1" appear to be exactly the same. Do you want "Before Sheet2" to be made to look like "After Sheet2"? Please provide more detail. How many and which sheets are you actually working with and what exactly do you want to do with those sheets?

[ahilty]

Yes - I tried to make it more clear by using red font on AFTER SHEET1.

"Sheet1" will not be affected; it is solely used to copy information from.

"After Sheet2 is the final product. How I would like it to look like.
- Note that the only column that needs to be sorted is column A. Column B (even though it wouldn't be difficult to also sort that as a secondary column should not be sorted. This is because job costs in that field that are related are normally kept beside one another (not in any alphabetical order).

Thanks again Mumps,

[Mumps1]

Let's try this attachment.