Vba code to remove and add subtotal in different sheets.

[Jagdev Singh]

Hi Experts,

I have recorded a macro which adds and remove subtotal from multiple sheets. Can we do it with VBA code.

Add subtotal code

Sheets("EL Listing").Activate
  Range("B5").Select
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(10, 11, 12 _
        ), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
   
    Sheets("PL Listing").Activate
    Range("B5").Select
  Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(10, 11, 12 _
        ), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Sheets("Med Mal Listing").Activate
    Range("B5").Select
  Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(10, 11, 12 _
        ), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Sheets("Legal Expense Listing").Activate
  Range("B5").Select
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(10, 11, 12 _
        ), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        Application.Calculation = xlCalculationAutomatic
    Sheets("PDBI Listing").Activate
    Range("B5").Select
    Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 9), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Remove subtotal:

Sheets("EL Listing").Select
    Selection.RemoveSubtotal
    Sheets("PL Listing").Select
    Selection.RemoveSubtotal
    Sheets("Med Mal Listing").Select
    Selection.RemoveSubtotal
    Sheets("PDBI Listing").Select
    Selection.RemoveSubtotal
    Sheets("Legal Expense Listing").Select
    Selection.RemoveSubtotal

Regards,
JD

 

[Luke M]

Hi JD,

How's this?

Sub Subtotals()
Dim ws As Worksheet
Application.ScreenUpdating = True
For Each ws In ThisWorkbook.Worksheets
    With ws.Range("B5")
        .RemoveSubtotal
        .Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(10, 11, 12), _
            Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    End With
Next ws
Application.ScreenUpdating = True
   
End Sub

 

[Jagdev Singh]

Hi Luke

I want to first remove the subtotal only from 5 sheets amongst the list of sheets available in the workbook.

After completing another tasks later I want to add the subtotal again in these sheets.

Please let me know if I am unclear with the requirements.

Regards,
JD

 

[Jagdev Singh]

Hi Luke

I reduce the code further. Can we loop it Sheetname wise.

Remove code -

 Sheets("EL Listing").UsedRange.RemoveSubtotal
    Sheets("PL Listing").UsedRange.RemoveSubtotal
    Sheets("Med Mal Listing").UsedRange.RemoveSubtotal
    Sheets("PDBI Listing").UsedRange.RemoveSubtotal
    Sheets("Legal Expense Listing").UsedRange.RemoveSubtotal

Add Code -

  Sheets("EL Listing").Range("B5").Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(10, 11, 12 _
        ), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Sheets("PL Listing").Range("B5").Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(10, 11, 12 _
        ), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Sheets("Med Mal Listing").Range("B5").Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(10, 11, 12 _
        ), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Sheets("Legal Expense Listing").Range("B5").Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(10, 11, 12 _
        ), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Sheets("PDBI Listing").Range("B5").Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(7, 8, 9), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Regards,
JD

 

[Luke M]

You can loop using an array, like this

Dim ws As Worksheet
For Each ws in Worksheets(Array("EL Listing", "PL Listing", "Med Mal Listing", "PDBI Listing", "Legal Expense Listing"))
ws.UsedRange.RemoveSubtotal
Next ws

 

[Jagdev Singh]

Thanks Luke

This is fitting into my requirement.

Regards,
JD