Macro For Automatically Creating A Subtotal

Darryl256 · Feb 24th 2007

I am a new member here, but going by the threads I have read so far hopefully someone is able to help me.

I have recently been to a excel course where the presenter was very fond of excel's SUBTOTAL formula that he wrote a macro so that he had a toolbar button similar to the built-in autosum function. Can anyone post the code that I need to paste into VBA for this to work?

If possible I would like to have the formula to have a Function Number of "9" (ie. SUM), but would stop/wait for me to select the cell range (either rows or columns).
In case you are wondering why I want this, I am an Accountant and sometimes I have to analysis cashbooks or cashflows and would like to have a subtotal for each section without have 12 additions for the year to date figure.

**royUK** · Feb 24th 2007

Re: Macro For Automatically Creating A Subtotal

I think [pt]*[/pt] would be more valuable,to use subtotals the data will first need sorting, not with Pivottables.

More to read on the subject of PivotTables in the Tutorials section here. If you are planning to learn VBA then also look at the Recordig macros tutorial there, to see how to add a menu Button. The Macro Recorder can also provide the basic code for subtotals

Code

Option Explicit


Sub SubTotal_amts()
    Dim rng    As Range
    On Error GoTo user_cancelled
    'select range for subtotalDim rng As Range
    Set rng = Application.InputBox("Select the Range to subtotal", Type:=8)


    'calculate subtotals
    rng.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
                 Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Exit Sub
user_cancelled:
    MsgBox "No selection made"


End Sub

Display More

Macro For Automatically Creating A Subtotal

Participate now!

Share