Excel VBA 複数列にSum関数で合計を作成する 

    Dim ObjThisSh  As Object
    Dim StrSum     As String
    Dim RowMax     As Long
    Set ObjThisSh = ThisWorkbook.Sheets("Sheet1")
    
    StrSum = "=SUM(C2:C10)"                '2行目から10行目を合計する

    With ObjThisSh

         RowMax = .Range("B1").End(xlDown).Row + 1 '合計追加行取得
            
        .Range("B" & RowMax).Value = "合計"
        
        .Range("C" & RowMax).Formula = StrSum  'SUM関数を「C11」入れる

        .Range("C" & RowMax).AutoFill Destination:=.Range("C" & RowMax).Resize(1, 5)  'D11～G11にコピー
    End With

    Set ObjThisSh = Nothing