Subtotal Function

[seenfresh]

I constantly have the need for using the subtotal function and am wondering if their is an easier way than just typing in the subtotal argument for each time I need it. For instance the sum function has a quick button on the tool bar... is their a way to insert a quick function for subtotal that would save time?

Any advice would be great!

Thanks in advance

 

[texasalynn]

I have this macro that does subtotals formula at the place of your cursor. So put this code into a macro file or your personal workbook, then add a button on your toolbar for this macro

Sub AutoSubtotal_Formula()
    Dim x As String
    Dim y As String
    
        x = ActiveCell.Offset(-1, 0).End(xlUp).Address
        y = ActiveCell.Offset(-1, 0).Address
        
        ActiveCell = "=SUBTOTAL(9," & x & ":" & y & ")"
        
End Sub

 

[seenfresh]

This is nice!

A couple of questions:

1] It this would basically replicate the sum function on the toolbar?
2] How can I add a button on my toolbar for this function?

Thank you for all your help!

Sean

 

[texasalynn]

to add a button you goto the
View - Toolbars - Customize
select the Commands tab and scroll down to find in the categories: Macros
there is a custom button
Drag the custom button onto your toolbar, right click on that button now on your toolbar and click, assign macro. The macros selection window will be displayed, click on the auto_subtotal macro.
Close the customize window.

Now when you click on that button it will run that macro.

 

[seenfresh]

Thank you very much! This is perfect just what I needed

Do you know if I can make is available to any workbook without manually putting in the code for each workbook I desire it's use for?

If I wanted to use it in other external workbooks I would need the workbook with the Macro to be Open!

Thanks again!

Cheers

 

[texasalynn]

the way I do my macros, is I have a file where I keep most all of them.
When you create a macro button and point to the macro name the macro button knows the file that has the macro code. Then when you close your "all" macros file and click on the icon button that will automatically open the file where the macro is kept.

The other option is saving your general macros to the personal workbook. I don't prefer this because it conflicts with other macros - I'm sure that others will tell you different reasons why to use personal workbook. I feel it's just a preference.

 

[seenfresh]

Thanks for all your help!

Cheers

Sean