Macro to insert 2 empty rows and row of heading after subtotal row

[New_To_Macros]

Hello,

I would like to create a macro, which will insert

• a subtotal after each change in either column B (Employment Status) or C (Type of Leave),
• bold the entire subtotal row,
• change the background of the subtotal row to grey,
• insert 3 rows after the subtotal and
• insert a line of headers before the beginning of the next set of data
• sort the data by largest to smallest in column F ($ OF LEAVE), then from A-Z in column A (Employee Name)

The data is not always the same, as sometimes some employees leave the firm and new employees join the firm.

Any assistance on how to create this macro is greatly appreciated. Thank you!

Original Table

Employee Name	Employment Status	Type of Leave	Rate/Hr	Hours of Leave	$ of Leave
Andrew Anderson	Full Time	Vacation	40.00	10.00	400.00
Birgit Braun	Full Time	Vacation	30.00	15.00	450.00
Charly Campbell	Part Time	Vacation	20.00	10.00	200.00
Donald Dennis	Full Time	Sick	40.00	20.00	800.00
Edward Ego	Student	Lieu Time	15.00	5.00	75.00
Fred Flint	Full Time	Lieu Time	30.00	15.00	450.00
George Gutenberg	Part Time	Lieu Time	20.00	10.00	200.00
Henry Hase	Part Time	Vacation	20.00	15.00	300.00
Ida Iglo	Student	Vacation	15.00	15.00	225.00

What the table should look like after running the macro:

 

[onlyadrafter]

Hello,

this gives the same results as your example.

Sub subtotal()
'SORT DATA
    Range("A14:F" & Range("F" & Rows.Count).End(xlUp).Row).Select
    Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, Key2:=Range("C14" _
        ), Order2:=xlDescending, Key3:=Range("F14"), Order3:=xlDescending, _
        Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal
'INSERT BLANK ROWS
    For MY_ROWS = Range("A" & Rows.Count).End(xlUp).Row To 15 Step -1
        If Range("B" & MY_ROWS).Value <> Range("B" & MY_ROWS - 1).Value Or _
            Range("C" & MY_ROWS).Value <> Range("C" & MY_ROWS - 1).Value Then
            For MY_ADD = 1 To 3
                Rows(MY_ROWS).Insert
            Next MY_ADD
        End If
    Next MY_ROWS
'COPY HEADER ROW
    For MY_ROWS = 14 To Range("A" & Rows.Count).End(xlUp).Row
        If IsEmpty(Range("A" & MY_ROWS).Value) And Not IsEmpty(Range("A" & MY_ROWS + 1).Value) _
            Then Rows(13).Copy Range("A" & MY_ROWS)
    Next MY_ROWS
'ADD SUBTOTAL TEXT
    For MY_ROWS = 14 To Range("A" & Rows.Count).End(xlUp).Row + 1
            If IsEmpty(Range("A" & MY_ROWS).Value) And IsEmpty(Range("A" & MY_ROWS + 1).Value) Then
            Range("A" & MY_ROWS).Value = "Subtotal of " & Range("B" & MY_ROWS - 1).Value & " " & _
                Range("C" & MY_ROWS - 1).Value
            Range("A" & MY_ROWS & ":F" & MY_ROWS).Interior.ColorIndex = 15
            Range("E" & MY_ROWS).Formula = "=SUM(E" & MY_ROWS - 1 & ":E" & Range("E" & MY_ROWS - 1).End(xlUp).Offset(1, 0).Row & ")"
            Range("F" & MY_ROWS).Formula = "=SUM(F" & MY_ROWS - 1 & ":F" & Range("F" & MY_ROWS - 1).End(xlUp).Offset(1, 0).Row & ")"
        End If
    Next MY_ROWS
End Sub

the code could be streamlined, but i'm off out soon.