Option Explicit

Public Sub BuildSummary()
    Dim wsSrc As Worksheet, wsDst As Worksheet
    Dim lastRow As Long, r As Long

    Set wsSrc = ThisWorkbook.Sheets("취합원가표")

    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets("집계자동화").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    Set wsDst = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets("취합원가표"))
    wsDst.Name = "집계자동화"

    lastRow = wsSrc.Cells(wsSrc.Rows.Count, 1).End(xlUp).Row

    r = 1
    Call WriteAggregate(wsSrc, wsDst, lastRow, 1, "[대분류별 금액 합계]", "대분류", r)
    r = r + 2
    Call WriteAggregate(wsSrc, wsDst, lastRow, 2, "[중분류별 금액 합계]", "중분류", r)
    r = r + 2
    Call WriteAggregate(wsSrc, wsDst, lastRow, 11, "[공급업체별 금액 합계]", "공급업체", r)

    wsDst.Columns("A:B").AutoFit
End Sub

Private Sub WriteAggregate(wsSrc As Worksheet, wsDst As Worksheet, lastRow As Long, _
                            keyCol As Long, title As String, label As String, ByRef startRow As Long)
    Dim dict As Object
    Dim i As Long
    Dim k As Variant
    Dim row As Long
    Set dict = CreateObject("Scripting.Dictionary")

    For i = 2 To lastRow
        k = wsSrc.Cells(i, keyCol).Value
        If Len(CStr(k)) > 0 Then
            If dict.Exists(k) Then
                dict(k) = dict(k) + wsSrc.Cells(i, 10).Value
            Else
                dict.Add k, wsSrc.Cells(i, 10).Value
            End If
        End If
    Next i

    wsDst.Cells(startRow, 1).Value = title
    wsDst.Cells(startRow, 1).Font.Bold = True
    wsDst.Cells(startRow + 1, 1).Value = label
    wsDst.Cells(startRow + 1, 2).Value = "금액 합계"
    With wsDst.Range(wsDst.Cells(startRow + 1, 1), wsDst.Cells(startRow + 1, 2))
        .Font.Bold = True
        .Interior.Color = RGB(220, 235, 245)
    End With

    row = startRow + 2
    For Each k In dict.Keys
        wsDst.Cells(row, 1).Value = k
        wsDst.Cells(row, 2).Value = dict(k)
        wsDst.Cells(row, 2).NumberFormat = "#,##0"
        row = row + 1
    Next k

    startRow = row - 1
End Sub