Option Explicit

Public Sub BuildCharts()
    Dim wsSum As Worksheet, wsCh As Worksheet
    Dim lastR As Long
    Dim startR As Long, endR As Long
    Dim topY As Long

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

    Set wsCh = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets("집계자동화"))
    wsCh.Name = "그래프자동화"
    Set wsSum = ThisWorkbook.Sheets("집계자동화")

    lastR = wsSum.Cells(wsSum.Rows.Count, 1).End(xlUp).Row
    topY = 10

    Call FindBlock(wsSum, lastR, "[대분류별 금액 합계]", startR, endR)
    Call AddChart(wsCh, wsSum, startR + 1, endR, "대분류별 금액 합계", 10, topY, xlColumnClustered)
    topY = topY + 300

    Call FindBlock(wsSum, lastR, "[중분류별 금액 합계]", startR, endR)
    Call AddChart(wsCh, wsSum, startR + 1, endR, "중분류별 금액 합계", 10, topY, xlBarClustered)
    topY = topY + 300

    Call FindBlock(wsSum, lastR, "[공급업체별 금액 합계]", startR, endR)
    Call AddChart(wsCh, wsSum, startR + 1, endR, "공급업체별 금액 합계", 10, topY, xlBarClustered)
End Sub

Private Sub FindBlock(ws As Worksheet, lastR As Long, title As String, ByRef startR As Long, ByRef endR As Long)
    Dim i As Long
    startR = 0: endR = 0
    For i = 1 To lastR
        If ws.Cells(i, 1).Value = title Then
            startR = i + 1
            Exit For
        End If
    Next i
    If startR = 0 Then Exit Sub
    endR = startR + 1
    Do While endR <= lastR
        If Len(CStr(ws.Cells(endR, 1).Value)) = 0 Then Exit Do
        If Left(CStr(ws.Cells(endR, 1).Value), 1) = "[" Then Exit Do
        endR = endR + 1
    Loop
    endR = endR - 1
End Sub

Private Sub AddChart(wsCh As Worksheet, wsSum As Worksheet, headerRow As Long, endRow As Long, _
                      title As String, leftPos As Long, topPos As Long, chartType As Long)
    Dim ch As ChartObject
    Set ch = wsCh.ChartObjects.Add(Left:=leftPos, Top:=topPos, Width:=560, Height:=280)
    ch.Chart.SetSourceData Source:=wsSum.Range(wsSum.Cells(headerRow, 1), wsSum.Cells(endRow, 2))
    ch.Chart.ChartType = chartType
    ch.Chart.HasTitle = True
    ch.Chart.ChartTitle.Text = title
    ch.Chart.HasLegend = False
End Sub