Attribute VB_Name = "ReportMacro"
' 월별 매출 시트를 모아 '보고서' 시트(지점별·월별 합계, 품목별 합계)를 만드는 매크로
' 넣는 법: 엑셀에서 Alt+F11 → 파일 → 파일 가져오기 → 이 파일(MakeReport.bas) 선택
' 실행법: 엑셀로 돌아와 Alt+F8 → MakeReport → 실행
'
' 이렇게 동작합니다.
' - 열려 있는 통합 문서의 모든 시트를 읽습니다. 이름이 '보고서'인 시트는 건너뜁니다.
' - 머리글은 위에서 5행 안에서 찾습니다. 열 순서가 달라도, 중간에 열이 추가돼도 이름으로 찾습니다.
' - 빈 행, '합계'·'소계'·'총계' 행은 건너뜁니다.
' - 금액이 비었거나 숫자가 아닌 행은 빼고, 몇 행인지 알려 줍니다.
' - '보고서' 시트가 이미 있으면 지우고 새로 만듭니다.
Option Explicit

Private Const REPORT_SHEET As String = "보고서"

Public Sub MakeReport()
    Dim msg As String
    msg = BuildReport()
    MsgBox msg, vbInformation, "보고서 만들기"
End Sub

' 결과 문장을 돌려줍니다(자동 실행·시간 측정용으로 따로 둠)
Public Function BuildReport() As String
    Dim t0 As Double: t0 = Timer
    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim byBranch As Object: Set byBranch = CreateObject("Scripting.Dictionary")
    Dim byProduct As Object: Set byProduct = CreateObject("Scripting.Dictionary")
    Dim months As New Collection
    Dim notes As String, used As Long, skipped As Long, bad As Long
    Dim ws As Worksheet, data As Variant
    Dim hRow As Long, cB As Long, cP As Long, cA As Long
    Dim r As Long, c As Long, key As String, b As String, p As String, amt As Variant
    Dim isBlank As Boolean, isTotal As Boolean, v As String

    Application.ScreenUpdating = False
    On Error GoTo Fail

    For Each ws In wb.Worksheets
        If Trim(ws.Name) = REPORT_SHEET Then GoTo NextSheet
        If Application.WorksheetFunction.CountA(ws.UsedRange) = 0 Then
            notes = notes & vbLf & ws.Name & ": 빈 시트라 건너뜀"
            GoTo NextSheet
        End If
        data = ws.UsedRange.Value
        If Not IsArray(data) Then GoTo NextSheet
        ' 머리글 찾기 (위 5행 안)
        hRow = 0
        For r = 1 To Application.Min(5, UBound(data, 1))
            cB = 0: cP = 0: cA = 0
            For c = 1 To UBound(data, 2)
                Select Case Trim(CStr(data(r, c) & ""))
                    Case "지점": cB = c
                    Case "품목": cP = c
                    Case "금액", "매출액", "판매금액": cA = c
                End Select
            Next c
            If cB > 0 And cP > 0 And cA > 0 Then hRow = r: Exit For
        Next r
        If hRow = 0 Then
            notes = notes & vbLf & ws.Name & ": 머리글(지점, 품목, 금액)을 찾지 못해 건너뜀"
            GoTo NextSheet
        End If
        months.Add ws.Name
        bad = 0
        For r = hRow + 1 To UBound(data, 1)
            isBlank = True: isTotal = False
            For c = 1 To UBound(data, 2)
                v = Replace(Trim(CStr(data(r, c) & "")), " ", "")
                If v <> "" Then isBlank = False
                If v = "합계" Or v = "소계" Or v = "총계" Then isTotal = True
            Next c
            If isBlank Or isTotal Then
                skipped = skipped + 1
            Else
                b = Trim(CStr(data(r, cB) & ""))
                p = Trim(CStr(data(r, cP) & ""))
                amt = data(r, cA)
                If VarType(amt) = vbString Then amt = Replace(Replace(amt, ",", ""), "원", "")
                If b = "" Or p = "" Or Not IsNumeric(amt) Or Trim(CStr(amt & "")) = "" Then
                    bad = bad + 1
                Else
                    key = b & vbTab & ws.Name
                    byBranch(key) = byBranch(key) + CDbl(amt)
                    If Not byBranch.Exists(b) Then byBranch(b) = 0
                    byProduct(p) = byProduct(p) + CDbl(amt)
                    used = used + 1
                End If
            End If
        Next r
        If bad > 0 Then notes = notes & vbLf & ws.Name & ": 지점·품목·금액이 비었거나 금액이 숫자가 아닌 행 " & bad & "개를 뺐습니다"
NextSheet:
    Next ws

    If months.Count = 0 Then
        BuildReport = "집계할 시트가 없습니다. 머리글에 지점, 품목, 금액 열이 있는지 확인하세요." & notes
        GoTo Done
    End If

    ' 보고서 시트 새로 만들기
    Application.DisplayAlerts = False
    On Error Resume Next
    wb.Worksheets(REPORT_SHEET).Delete
    On Error GoTo Fail
    Application.DisplayAlerts = True
    Dim rep As Worksheet
    Set rep = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    rep.Name = REPORT_SHEET

    ' 표1: 지점 × 월
    Dim nM As Long: nM = months.Count
    Dim branches As New Collection, k As Variant
    For Each k In byBranch.Keys
        If InStr(k, vbTab) = 0 Then branches.Add k
    Next k
    Dim out1() As Variant, i As Long, j As Long, s As Double
    ReDim out1(1 To branches.Count + 2, 1 To nM + 2)
    out1(1, 1) = "지점"
    For j = 1 To nM: out1(1, j + 1) = months(j): Next j
    out1(1, nM + 2) = "합계"
    For i = 1 To branches.Count
        out1(i + 1, 1) = branches(i)
        s = 0
        For j = 1 To nM
            out1(i + 1, j + 1) = byBranch(branches(i) & vbTab & months(j)) + 0
            s = s + out1(i + 1, j + 1)
        Next j
        out1(i + 1, nM + 2) = s
    Next i
    out1(branches.Count + 2, 1) = "합계"
    For j = 2 To nM + 2
        s = 0
        For i = 2 To branches.Count + 1: s = s + out1(i, j): Next i
        out1(branches.Count + 2, j) = s
    Next j
    rep.Range("A1").Resize(branches.Count + 2, nM + 2).Value = out1

    ' 표2: 품목 (금액 큰 순)
    Dim nP As Long: nP = byProduct.Count
    Dim pk() As Variant, pv() As Double, tk As Variant, tv As Double
    ReDim pk(1 To nP): ReDim pv(1 To nP)
    i = 0
    For Each k In byProduct.Keys
        i = i + 1: pk(i) = k: pv(i) = byProduct(k)
    Next k
    For i = 1 To nP - 1
        For j = i + 1 To nP
            If pv(j) > pv(i) Then
                tv = pv(i): pv(i) = pv(j): pv(j) = tv
                tk = pk(i): pk(i) = pk(j): pk(j) = tk
            End If
        Next j
    Next i
    Dim top2 As Long: top2 = branches.Count + 4
    Dim out2() As Variant
    ReDim out2(1 To nP + 1, 1 To 2)
    out2(1, 1) = "품목": out2(1, 2) = "금액"
    For i = 1 To nP: out2(i + 1, 1) = pk(i): out2(i + 1, 2) = pv(i): Next i
    rep.Cells(top2, 1).Resize(nP + 1, 2).Value = out2

    ' 서식
    rep.Range("B2").Resize(branches.Count + 1, nM + 1).NumberFormat = "#,##0"
    rep.Cells(top2 + 1, 2).Resize(nP, 1).NumberFormat = "#,##0"
    rep.Range("A1").Resize(1, nM + 2).Font.Bold = True
    rep.Range("A1").Resize(1, nM + 2).Interior.Color = RGB(221, 235, 247)
    rep.Cells(branches.Count + 2, 1).Resize(1, nM + 2).Font.Bold = True
    rep.Cells(top2, 1).Resize(1, 2).Font.Bold = True
    rep.Cells(top2, 1).Resize(1, 2).Interior.Color = RGB(221, 235, 247)
    rep.Columns("A").ColumnWidth = 12
    rep.Columns("B").Resize(, nM + 1).ColumnWidth = 16

    Dim names As String
    For j = 1 To nM: names = names & IIf(j > 1, ", ", "") & months(j): Next j
    BuildReport = "시트 " & nM & "개(" & names & "), 집계한 행 " & used & "개, 건너뛴 빈·합계 행 " & skipped & "개, " & _
        Format(Timer - t0, "0.00") & "초" & IIf(notes <> "", vbLf & "확인 필요:" & notes, "")
    rep.Activate
    GoTo Done

Fail:
    Dim where As String: where = "-"
    If Not ws Is Nothing Then where = ws.Name
    BuildReport = "중간에 멈췄습니다: " & Err.Description & " (시트: " & where & ")"
Done:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Function