Subtotals by Group 1

[JSMITH242B]

Hi All,
I have an Excel template which will be populated dynamically with data from my .ASP Page. This it does.

The format of the template is as follows:

Column B: Branch
Column C: Account No
Column E: Turnover

At the moment, I have a grand total but will need to have subtotals for each Branch.
How can I achieve this using VBA

Kind regards


Example is as follows:
Branch Account No Turnover
Cambridge 1 50.00
Cambridge 2 48.00
Cambridge 3 15.00
Harlow 1617 25.00
Harlow 4556 45.00

Totals: 183.00


I need the report to look like this
Branch Account No Turnover
Cambridge 1 50.00
Cambridge 2 48.00
Cambridge 3 15.00
Total 113.00

Harlow 1617 25.00
Harlow 4556 45.00
Total 70.00

Totals: 183.00

 

[nnett]

If your data is already sorted in branch order this should do what you want.

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, _
TotalList:=Array(3), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True

 

[JSMITH242B]

Hello nnett,
Thank you!! This does what I am after. However I have coded this as a macro to see it working. Because of this, I get the prompt to enable/disable macros - which I don't want the users to get.

Therefore, I would like to place this code in an event maybe onOpen/onActivate so that it runs before/as the spreadsheet opens without the user being prompted.

How can I achieve this?
Thank you.

 

[PHV]

I get the prompt to enable/disable macros
This is part of the Excel security model and you can't change it by code.
You may consider digitally signing your VBA.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[JSMITH242B]

Hello PHV,
I've set the security level to 'Low' which prevents this.


Thanks.