Macro to insert a blank line after each subtotal

[fmw5162022]

Hello,

Currently, I have a macro for sorting and subtotaling a list of customers and their daily total sales. Those work as expected, providing the results I desire.

I need to insert a blank row AFTER each subtotal, and am unable to accomplish this.
Below are my Sort and Subtotal macros:

Sub SortSalesDetail()

' SortSalesDetail Macro
' Sort Sales Detail
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "####-##-##_Tickets" Then
        ws.[A1].CurrentRegion.Sort key1:=ws.[C1], Header:=xlYes, Key2:=ws.[E1], Header:=xlYes
ws.Name = ws.Name & "_s"

    End If
Next

    ActiveWindow.SmallScroll Down:=-63
End Sub

Sub Subtotal()
'
' Subtotal Macro
'

    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(9), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
 
End Sub

Thanking you very much in advance for your help!!

 

[BSALV]

your subtotal works on changes of the 3rd column, so if you insert an empty row, that's another value, so needs a new subtotal ... .
As workaround, you can make the row of the subtotal twice it normal height and the vertical alignement to the top.
Visual that 'll give the same effect. Is that a solution ?

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added them for you this time. ?

See if this does what you want after the Subtotal code** has been run.
Test with a copy of your workbook.

Sub Insert_Blank_Rows()
  Dim c As Range
  
  Application.ScreenUpdating = False
  For Each c In Columns("B").SpecialCells(xlBlanks)
    If Right(c.Offset(, 1).Value, 6) = " Total" Then c.Offset(1).EntireRow.Insert
  Next c
  Application.ScreenUpdating = True
End Sub

**
It is never a good idea to use a word that vba already has a special meaning for as a variable, procedure or module name. Therefore I would recommend renaming your 'Subtotal' procedure.