Sort out list and sum each different category

[errtu]

I have a list of commodities and each one has a number. This list is all mixed up. The macro should sort them out, put all the commodities that a re the same together and sum their numbers. So , I'll end up with a sorted out list with totals for each commodity.

maybe move them to a designated pair of columns for each commodity?

 

[Leith Ross]

Hello errtu,

Have you tried using the SubTotals... under Data on the main menu? This will automatically sort the commodities alphabetically and provides totals for each.

 

[errtu]

Hello errtu,

Have you tried using the SubTotals... under Data on the main menu? This will automatically sort the commodities alphabetically and provides totals for each.

ok, i'm trying:

 Private Sub PrintRoutine_Click()
Application.ScreenUpdating = False
 Columns("C:C").Select
    ActiveWorkbook.Worksheets("DDE").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DDE").Sort.SortFields.Add Key:=Range("C2:C100"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DDE").Sort
        .SetRange Range("A1:D100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Sheets("DDE").Range("a1:d100").Select
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(1), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True


Application.ScreenUpdating = True
        
    End Sub

I get run time error 1004
select method o range class failed.

I am trying to make this happen while i am in another worksheet.

 

[Leith Ross]

Hello errtu,

It is rare in VBA that you will ever need to select an object before performing an action on it. Try this method insted:

With Worksheets("DDE").Range("a1:d100")
    .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(1), _
     Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With

 

[errtu]

Hello errtu,

It is rare in VBA that you will ever need to select an object before performing an action on it. Try this method insted:

With Worksheets("DDE").Range("a1:d100")
    .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(1), _
     Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With

thanks for the tip