Sum values in cell from variable rows (rows are added and deleted)

Hello all. Need some help summing data in several columns on a sheet that will have rows added and removed. In the attached workbook, the SNSs sheet has two sums (currently rows 14 and 15) for certain rows in columns D, F, H, J, L, N, P. The two sums for each column correlate to the the type of data being summed, so the Pack sum is the sum of every Pack value in the respective column. The same for Council summing all the Council data. The SUM calculation in row 3 columns D, F, H, J, L, N, P also need to be variable to sum all the rows in their respective columns (sums from the column to the left). Since this is basically summing every other row, I am at a total loss how to add this to the code that adds the Scout to the sheet, or set the formulas in the cells themselves. Any help would be appreciated.

Code that adds the Scouts to the sheet is below:

Private Sub cmdStart_Click()
    Dim shtName As String
'insert row and apply formulas and formatting on SNSs Sheet
    Sheets("SNSs").Activate
    Rows("9:10").EntireRow.Insert
    Range("A9:A10").Activate
  '  Range("A10").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("A9").Formula = "='" & shtName & "'!A2 & "" "" & '" & shtName & "'!B2"
    ActiveSheet.Hyperlinks.Add Anchor:=Range("A9"), Address:="", SubAddress:= _
            "= ('" & shtName & "'!A2)"
    Range("B7:Q8").Copy
    Range("B9:Q10").PasteSpecial
    End Sub

Thanks,
Andrew