views:

290

answers:

1

I have data of this form:

Category      Source      Amount
Dues          FTW         $100
Donations     ODP         $20
Donations     IOI         $33
Dues          MMK         $124

Where there is no sort order. I want a VBA macro to cycle through the range, output a list of distinct values, with the subtotals for each. For the above data, it would look like this:

Category      Total Amount
Dues          $224
Donations     $55

How can I do this? Also, is there a way to make the macro run every time the table above is updated, or is it necessary for the user to click a button?

Note: the categories won't be known at "compile time".

+2  A: 

You may want to use a built in Excel feature to do this. Looping can take a long time and be problematic if you have a lot of values to loop through.

Something like the following might get you started on creating a pivot table (from http://www.ozgrid.com/News/pivot-tables.htm)

Sub MakeTable()
Dim Pt As PivotTable
Dim strField As String

    'Pass heading to a String variable
    strField = Selection.Cells(1, 1).Text

    'Name the list range
    Range(Selection, Selection.End(xlDown)).Name = "Items"

    'Create the Pivot Table based off our named list range.
    'TableDestination:="" will force it onto a new sheet
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
        SourceData:="=Items").CreatePivotTable TableDestination:="", _
            TableName:="ItemList"

    'Set a Pivot Table variable to our new Pivot Table
    Set Pt = ActiveSheet.PivotTables("ItemList")

    'Place the Pivot Table to Start from A3 on the new sheet
    ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1)

    'Move the list heading to the Row Field
    Pt.AddFields RowFields:=strField
    'Move the list heading to the Data Field
    Pt.PivotFields(strField).Orientation = xlDataField
End Sub

This is for subtotals (though I much prefer pivot tables) http://msdn.microsoft.com/en-us/library/aa213577%28office.11%29.aspx


EDIT: I reread and have the following thoughts. Set up the pivot table on a separate sheet (without using any code) then put the following code on the sheet that has the pivot table so that the table will update everytime the sheet is selected.

    Private Sub Worksheet_Activate()

    Dim pt As PivotTable

        'change "MiPivot" to the name of your pivot table
        Set pt = ActiveSheet.PivotTables("MyPivot")

        pt.RefreshTable

    End Sub


Edit #2 Refresh all pivot tables on sheet http://www.ozgrid.com/VBA/pivot-table-refresh.htm

Private Sub Worksheet_Activate()    
    Dim pt As PivotTable

    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt
End Sub

The link has multiple options on how to refresh pivot tables in the sheet/workbook.

guitarthrower
I agree, Pivot Tables is what you are after
Christian Payne
how do I find the name of the pivot table/chart?
Rosarch
Right click on the pivot table and select pivot table options. That will show you the current name and allow you to change the name if you want.
guitarthrower
and how do I make it so `Worksheet_Activate` is called every time the sheet is selected?
Rosarch
also, is there a way to make it more general, like "refresh all pivot tables on this sheet?"
Rosarch
if you place the worksheet_activate code in the sheet you want it to run on, it will be triggered every time the user visits that sheet.
guitarthrower
I've added edit #2 to show refresh all pivot tables on this sheet. and added a link that describes a lot more pivot table refresh options.
guitarthrower