Customizing subtotals in Excel

[thelocaluk]

Hi everyone, I have an Excel form written in vb that adds data onto a sheet.
How do I get subtotals automatically added to the sheet at the end of each month?

Thanks

[Webtest]

This is Excel VBA ... I hope it helps ...

Option Explicit
Sub Macro1()
    Dim aRange As Range

    'The range to apply subtotals can be calculated programmatically
    Set aRange = Range("A1:C14")
    
    'Turn On Subtotals in the range
    'This groups on Column A ... figure it out by Recording a macro to do what you want.
    aRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

[thelocaluk]

Thanks for your reply Webtest, I can see how that works but how do I get it to subtotal at the end of each month only?

[Webtest]

I don't understand what you are getting at. Do you mean that you don't want the Grand Total row ... ony the Monthly Subtotal rows? If that is the case, you will have to do the Subtotal operation, and then search for and delete the "Grand Total" row.

As far as I know, there is no way to prevent the Grand Total row from being created.