흩어진 매크로를 버튼 하나로 — VBA 원클릭 보고서 자동화

데이터를 확인하고, 피벗 테이블을 만들고, 서식을 적용한 뒤 PDF로 저장하는 일을 매번 따로 실행하면 순서를 놓치기 쉽습니다. 마스터 매크로는 이 작업들을 정해진 순서로 호출하는 진입점입니다. 버튼에는 보고서_원클릭 하나만 연결합니다.

2026-10-01 수정 안내: 중복된 매크로를 하나로 통합하고 피벗 필드, PDF 대상 시트, 오류 처리 설명을 바로잡았습니다. 이 예제의 범위는 이미 준비된 로컬 데이터의 계산 → 피벗 구성 → 조건부 서식 → PDF 저장입니다. 외부 연결의 새로 고침 완료까지 자동으로 보장하는 코드는 아닙니다. 기존 ZIP은 현재 본문과 달라 다운로드 연결을 내려두었습니다.

1. 입력과 출력: 자동화의 시작과 끝

1.1 입력 조건

• 매크로를 넣은 통합 문서에 데이터, 분석, 설정 시트를 만듭니다. 코드는 ThisWorkbook을 기준으로 동작합니다.
• 데이터!A1:F1의 머리글을 순서대로 일자, 품목, 판매량, 단가, 총액, 지역으로 입력합니다. 중간에 완전히 빈 행·열이나 합계 행을 넣지 않습니다. CurrentRegion 밖의 데이터는 집계되지 않습니다.
• 판매량·단가·총액은 숫자 셀이어야 합니다. 숫자처럼 보이는 텍스트는 거부합니다. 총액은 미리 입력하거나 같은 시트의 판매량×단가 수식으로 준비합니다.
• 분석은 이 예제만 사용하는 전용 출력 시트입니다. 첫 실행에는 비워두고, 이후에도 다른 표·메모·피벗을 추가하지 않습니다. 기존 예제 피벗의 필터·정렬·조건부 서식은 다시 구성됩니다.
• C:\Reports 폴더를 미리 만들고 쓰기 권한을 확인합니다. PDF를 저장하지 않으면 이 폴더는 필요하지 않습니다.

1.2 설정과 출력

셀에는 작은따옴표 없이 TRUE 또는 FALSE를 입력합니다. 텍스트 “TRUE”, 숫자 1, 빈 셀은 설정 오류로 처리합니다. 세 값이 모두 FALSE이면 보고서를 만들지 않습니다.

• 분석 시트 A3에 월간_매출_분석 피벗을 배치합니다. 행은 품목, 열은 지역, 값은 총액의 합계입니다. 피벗 이름과 달리 월별 그룹화나 날짜 필터를 자동 적용하지는 않습니다.
• PDF 이름은 C:\Reports\주간_보고서_YYYYMMDD_HHNNSS.pdf입니다. 날짜와 시각은 실행 시점 기준이며, 같은 이름이 있으면 덮어쓰지 않고 중단합니다.
• 오류가 나면 통합 문서 폴더의 Macro_Log.txt에 기록을 시도합니다. 로그 저장도 실패하면 메시지로 알립니다.

2. 전제·환경·버전: 실행 가능한 조건

대상 환경: VBA와 PDF 내보내기를 지원하는 Windows 데스크톱 Excel, 로컬 폴더에 저장한 .xlsm 파일입니다. 경로가 Windows 형식이므로 웹용 Excel이나 Mac에 그대로 적용하는 예제가 아닙니다. 읽기 전용·보호된 시트, 외부 연결, 네트워크·클라우드 URL 경로는 이 기본 예제의 범위에서 제외합니다.

검토 범위: 코드의 중복, 호출 관계, 입력·출력 설명을 정적 점검하고 아래 합계는 연습 데이터로 계산했습니다. 실제 Excel 실행 검증은 수행하지 않았습니다. 특정 버전에서의 성공이나 실행 시간·파일 크기를 보장하지 않습니다. 먼저 복사본에서 VBA 편집기의 컴파일과 5절의 테스트를 수행하세요.

3. 구체 절차·코드: 마스터 매크로 구현

3.1 코드 넣기와 버튼 연결

1. 빈 연습용 통합 문서를 .xlsm으로 저장하고 위의 세 시트와 설정을 준비합니다.
2. Alt+F11로 VBA 편집기를 열고 삽입 → 모듈을 선택합니다. 아래 코드 전체를 같은 표준 모듈에 한 번만 붙여 넣습니다. 이전 버전의 같은 이름 Sub와 함께 넣지 않습니다.
3. 디버그 → VBAProject 컴파일을 실행하고, 데이터와 출력 폴더를 확인합니다.
4. Excel의 개발 도구 → 삽입 → 단추(양식 컨트롤)를 데이터 또는 설정 시트에 추가해 보고서_원클릭을 지정합니다. 분석 시트에는 버튼을 두지 않습니다.

3.2 전체 코드: 진입점과 보조 함수

작업 중 오류가 나면 나머지 작업을 중단하고 Excel의 계산 모드·이벤트·화면 갱신·상태 표시줄을 실행 전 값으로 복원하도록 구성했습니다. 경고창 설정은 변경하지 않습니다. 로그 실패가 정리 절차를 가로막지 않도록 로그 함수 안에서 별도로 처리합니다.

Option Explicit

Public Sub 보고서_원클릭()
    Dim wb As Workbook
    Dim wsData As Worksheet, wsOut As Worksheet, wsSet As Worksheet
    Dim src As Range, pt As PivotTable
    Dim oldCalc As XlCalculation, oldScreen As Boolean
    Dim oldEvents As Boolean, oldStatus As Variant
    Dim stateSaved As Boolean
    Dim doCalc As Boolean, doPivot As Boolean, doPDF As Boolean
    Dim resultMsg As String, stepName As String, pdfPath As String
    Dim errNum As Long, errDesc As String, logOK As Boolean
    Dim failed As Boolean, restoreFailed As Boolean

    On Error GoTo ErrHandler
    Set wb = ThisWorkbook
    oldCalc = Application.Calculation
    oldScreen = Application.ScreenUpdating
    oldEvents = Application.EnableEvents
    oldStatus = Application.StatusBar
    stateSaved = True

    stepName = "입력과 설정 확인"
    Set wsData = wb.Worksheets("데이터")
    Set wsOut = wb.Worksheets("분석")
    Set wsSet = wb.Worksheets("설정")
    doCalc = 설정_읽기(wsSet.Range("B2"))
    doPivot = 설정_읽기(wsSet.Range("B3"))
    doPDF = 설정_읽기(wsSet.Range("B4"))
    If doPDF And Not doPivot Then
        Err.Raise vbObjectError + 1001, , "PDF 저장 시 B3도 TRUE여야 합니다."
    End If
    If wb.Path = "" Then
        Err.Raise vbObjectError + 1002, , "먼저 로컬 폴더에 .xlsm으로 저장하세요."
    End If
    If doPDF Then
        If (GetAttr("C:\Reports") And vbDirectory) = 0 Then
            Err.Raise vbObjectError + 1003, , "C:\Reports 폴더를 확인하세요."
        End If
    End If

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "보고서 생성 중..."

    If doCalc Then
        stepName = "데이터 시트 계산"
        wsData.Calculate
    End If
    If doPivot Then
        stepName = "원본 데이터 검증"
        Set src = 원본_범위(wsData)
        stepName = "피벗 구성"
        Set pt = 피벗_구성(wb, wsOut, src)
        stepName = "조건부 서식"
        조건부서식_적용 pt
    End If
    If doPDF Then
        stepName = "분석 시트 PDF 저장"
        pdfPath = PDF_저장(wsOut, pt)
    End If
    resultMsg = "선택한 작업이 완료되었습니다."
    If pdfPath <> "" Then resultMsg = resultMsg & vbCrLf & pdfPath
    GoTo CleanExit

ErrHandler:
    failed = True
    errNum = Err.Number
    errDesc = Err.Description
    logOK = 로그_쓰기(stepName, errNum, errDesc)
    resultMsg = "작업 중단: " & stepName & vbCrLf & _
                CStr(errNum) & " - " & errDesc
    If Not logOK Then
        resultMsg = resultMsg & vbCrLf & "로그 저장도 실패했습니다. 오류 내용을 따로 기록하세요."
    End If
    Resume CleanExit

CleanExit:
    ' 각 설정의 복원을 시도하며 한 항목 실패로 나머지를 건너뛰지 않는다.
    On Error Resume Next
    If stateSaved Then
        Err.Clear
        Application.Calculation = oldCalc
        If Err.Number <> 0 Then restoreFailed = True
        Err.Clear
        Application.EnableEvents = oldEvents
        If Err.Number <> 0 Then restoreFailed = True
        Err.Clear
        Application.ScreenUpdating = oldScreen
        If Err.Number <> 0 Then restoreFailed = True
        Err.Clear
        Application.StatusBar = oldStatus
        If Err.Number <> 0 Then restoreFailed = True
    End If
    On Error GoTo 0
    If restoreFailed Then
        resultMsg = resultMsg & vbCrLf & "Excel 설정 일부를 복원하지 못했습니다. 수동 확인이 필요합니다."
    End If
    If failed Or restoreFailed Then
        MsgBox resultMsg, vbExclamation, "실행 결과 확인"
    Else
        MsgBox resultMsg, vbInformation, "작업 완료"
    End If
End Sub

Private Function 설정_읽기(ByVal cell As Range) As Boolean
    If VarType(cell.Value2) <> vbBoolean Then
        Err.Raise vbObjectError + 1010, , _
                  "설정!" & cell.Address(False, False) & "에 TRUE 또는 FALSE를 입력하세요."
    End If
    설정_읽기 = cell.Value2
End Function

Private Function 원본_범위(ByVal ws As Worksheet) As Range
    Dim src As Range, headers As Variant
    Dim i As Long, r As Long, v As Variant
    Set src = ws.Range("A1").CurrentRegion
    headers = Array("일자", "품목", "판매량", "단가", "총액", "지역")
    If src.Columns.Count <> 6 Or src.Rows.Count < 2 Then
        Err.Raise vbObjectError + 1020, , "A1:F열의 머리글과 데이터 행을 확인하세요."
    End If
    For i = 0 To 5
        v = src.Cells(1, i + 1).Value2
        If IsError(v) Then Err.Raise vbObjectError + 1021, , "머리글에 오류가 있습니다."
        If CStr(v) <> headers(i) Then
            Err.Raise vbObjectError + 1022, , "머리글 순서: 일자, 품목, 판매량, 단가, 총액, 지역"
        End If
    Next i
    For r = 2 To src.Rows.Count
        For i = 1 To 6
            v = src.Cells(r, i).Value2
            If IsError(v) Then Err.Raise vbObjectError + 1023, , "원본에 셀 오류가 있습니다."
            If Len(Trim$(CStr(v))) = 0 Then
                Err.Raise vbObjectError + 1024, , "원본에 빈 셀이 있습니다: " & src.Cells(r, i).Address
            End If
        Next i
    Next r
    For i = 3 To 5
        If Application.WorksheetFunction.Count( _
            src.Cells(2, i).Resize(src.Rows.Count - 1, 1)) <> src.Rows.Count - 1 Then
            Err.Raise vbObjectError + 1025, , "판매량·단가·총액은 텍스트가 아닌 숫자여야 합니다."
        End If
    Next i
    Set 원본_범위 = src
End Function

Private Function 피벗_구성(ByVal wb As Workbook, ByVal ws As Worksheet, _
                            ByVal src As Range) As PivotTable
    Dim cache As PivotCache, pt As PivotTable, item As PivotTable
    For Each item In ws.PivotTables
        If item.Name <> "월간_매출_분석" Then
            Err.Raise vbObjectError + 1030, , "분석 시트는 이 예제의 전용 출력 시트로 사용하세요."
        End If
        Set pt = item
    Next item
    If pt Is Nothing Then
        If Application.WorksheetFunction.CountA(ws.UsedRange) > 0 Then
            Err.Raise vbObjectError + 1031, , "첫 실행에는 비어 있는 분석 시트가 필요합니다."
        End If
    Else
        If pt.PivotCache.SourceType <> xlDatabase Or _
           pt.TableRange2.Cells(1, 1).Address <> "$A$3" Then
            Err.Raise vbObjectError + 1032, , "기존 피벗의 원본 형식과 시작 위치 A3를 확인하세요."
        End If
    End If

    Set cache = wb.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=src.Address(ReferenceStyle:=xlR1C1, External:=True))
    If pt Is Nothing Then
        Set pt = cache.CreatePivotTable(TableDestination:=ws.Range("A3"), _
                                       TableName:="월간_매출_분석")
    Else
        pt.TableRange2.FormatConditions.Delete
        pt.ClearTable
        pt.ChangePivotCache cache
    End If
    With pt
        With .PivotFields("품목")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("지역")
            .Orientation = xlColumnField
            .Position = 1
        End With
        .AddDataField .PivotFields("총액"), "총액 합계", xlSum
        .DataFields(1).NumberFormat = "#,##0"
        .RowGrand = True
        .ColumnGrand = True
        .RefreshTable
    End With
    ws.Range("A1").Value = "품목·지역별 매출 보고서"
    pt.TableRange2.Columns.AutoFit
    Set 피벗_구성 = pt
End Function

Private Sub 조건부서식_적용(ByVal pt As PivotTable)
    Dim body As Range
    Set body = pt.DataBodyRange
    If body Is Nothing Then Exit Sub
    body.FormatConditions.Delete
    body.FormatConditions.AddDatabar
End Sub

Private Function PDF_저장(ByVal ws As Worksheet, ByVal pt As PivotTable) As String
    Dim filePath As String, oldArea As String
    Dim n As Long, d As String
    oldArea = ws.PageSetup.PrintArea
    On Error GoTo Failed
    filePath = "C:\Reports\주간_보고서_" & Format$(Now, "yyyymmdd_hhnnss") & ".pdf"
    If Len(Dir$(filePath, vbNormal Or vbReadOnly Or vbHidden Or vbSystem Or vbDirectory)) > 0 Then
        Err.Raise vbObjectError + 1040, , "같은 이름의 PDF가 있습니다. 잠시 후 다시 실행하세요."
    End If
    ws.PageSetup.PrintArea = ws.Range("A1", _
        pt.TableRange2.Cells(pt.TableRange2.Rows.Count, pt.TableRange2.Columns.Count)).Address
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
    ws.PageSetup.PrintArea = oldArea
    PDF_저장 = filePath
    Exit Function
Failed:
    n = Err.Number
    d = Err.Description
    On Error Resume Next
    ws.PageSetup.PrintArea = oldArea
    On Error GoTo 0
    Err.Raise n, "PDF_저장", d
End Function

Private Function 로그_쓰기(ByVal stepName As String, ByVal n As Long, _
                            ByVal description As String) As Boolean
    Dim f As Integer, opened As Boolean
    On Error GoTo Failed
    If ThisWorkbook.Path = "" Then Exit Function
    f = FreeFile
    Open ThisWorkbook.Path & "\Macro_Log.txt" For Append As #f
    opened = True
    Print #f, Format$(Now, "yyyy-mm-dd hh:nn:ss") & " | " & stepName & _
              " | " & CStr(n) & " | " & description
    Close #f
    opened = False
    로그_쓰기 = True
    Exit Function
Failed:
    On Error Resume Next
    If opened Then Close #f
    로그_쓰기 = False
End Function

3.3 이 코드가 하는 일과 하지 않는 일

• wsData.Calculate는 데이터 시트의 수식을 계산합니다. 외부 파일·쿼리를 가져오지 않습니다. 다른 시트의 계산 순서에 의존하는 모델은 먼저 별도로 계산해 값이 준비되었는지 확인하세요.
• 피벗_구성은 새 캐시를 만들고 필드를 명시적으로 배치합니다. 기존 피벗은 ClearTable로 필드·필터·정렬을 초기화한 뒤 새 캐시로 바꿉니다. 캐시 재사용 최적화 예제는 아니므로 반복 운영 시 파일 크기와 메모리를 확인해야 합니다.
• 조건부서식_적용은 피벗 값 영역에 데이터 막대를 추가합니다. 합계 셀이 포함되는 영역에서는 합계도 비교 대상이 될 수 있습니다.
• PDF_저장은 전달받은 분석 시트만 내보냅니다. 임시 인쇄 영역은 제목부터 피벗 끝까지이며, 내보낸 뒤 기존 인쇄 영역 복원을 시도합니다. 용지·방향·배율은 분석 시트의 페이지 설정을 사용합니다.

4. 구체 입력·실행 동작·예상 출력

4.1 입력할 연습 데이터

아래 데이터는 머리글 1행과 데이터 10행이므로 범위는 A1:F11입니다. 총액 값은 판매량×단가와 일치하도록 적었습니다.

4.2 실행 순서

B2:B4를 모두 TRUE로 설정하고 버튼을 누르면 입력 확인 → 데이터 시트 계산 → 원본 검증 → 피벗 구성 → 조건부 서식 → PDF 저장 순서로 진행합니다. 한 단계라도 실패하면 뒤 단계로 넘어가지 않습니다. PDF 저장 실패 전까지 만들어진 피벗은 남을 수 있습니다.

4.3 수동 계산으로 대조할 값

이 입력에서는 품목마다 지역이 하나씩이므로 서울 12,000, 부산 6,900, 대구 1,750, 광주 1,650으로도 대조할 수 있습니다. 표시 순서는 Excel의 정렬에 따라 달라질 수 있습니다. 이 값은 입력 데이터의 산술 검산 결과이며, Excel에서 실행해 캡처한 결과는 아닙니다.

정상 실행 후에는 실제 생성된 PDF를 열어 제목·피벗·페이지 나눔을 확인합니다. 정상 종료 시 오류 로그를 새로 쓰지 않으며, 이전 로그는 그대로 남습니다. 실행 시간과 PDF 크기는 환경·서식에 따라 달라집니다.

5. 확인 대상·방법·합격 기준

1. 컴파일: 표준 모듈에 한 번만 붙여 넣고 컴파일 오류가 없는지 확인합니다. 누락된 함수나 중복된 프로시저 이름이 있으면 실행하지 않습니다.
2. 기본 실행: 빈 분석 시트와 연습 데이터로 실행하여 품목별 합계와 전체 22,300을 대조합니다. 다른 시트가 선택된 상태에서도 분석 시트가 PDF로 저장되는지 확인합니다.
3. 재실행: 첫 행 총액을 6,000에서 6,100으로 바꾸면 노트북 12,100, 전체 22,400이 되는지 확인합니다. 원래 값을 복구합니다. 이는 집계 갱신 테스트이며 판매량×단가 검증을 대신하지 않습니다.
4. 설정 복원: 실행 전 계산 모드 등 설정을 기록하고 정상·오류 종료 후 같은 값인지 확인합니다. 수동 계산을 자동 계산으로 강제 변경하지 않아야 합니다.
5. 실패 경로: 복사본에서 머리글을 바꾸거나 총액을 텍스트로 입력해 중단 메시지와 로그를 확인합니다. B3=FALSE·B4=TRUE 조합도 피벗·PDF 생성 전에 중단되어야 합니다.
6. 저장 오류: 연습용 경로가 없는 상태나 쓰기 권한이 없는 환경에서는 완료 메시지 대신 오류가 나야 합니다. PDF가 열려 있거나 저장이 부분적으로 실패했다면 출력 폴더도 직접 확인합니다.

6. 실패와 복구: 문제가 생겼을 때

• 시트·머리글·설정 오류: 메시지에 표시된 항목을 고친 뒤 다시 실행합니다. 숫자와 숫자 텍스트를 구분하세요.
• 폴더·권한 오류: C:\Reports 폴더와 저장 권한을 확인합니다. 로그 저장 위치는 통합 문서 폴더이므로 그 폴더의 쓰기 권한도 필요합니다.
• 피벗 오류: 분석 시트가 전용 출력 시트인지, 기존 피벗이 이 예제의 이름·시작 위치·일반 셀 범위 원본을 사용하는지 확인합니다. 첫 연습은 빈 분석 시트로 시작하는 편이 명확합니다.
• 복원 범위: Excel 응용 프로그램 설정의 복원은 데이터 롤백이 아닙니다. 피벗 변경이나 이미 저장된 PDF를 자동 취소하지 않으며, Excel 강제 종료·VBA의 End 명령 등으로 정리 코드가 실행되지 않으면 설정 복원도 보장할 수 없습니다.

원본은 별도 보관하고 복사본에서 실행하세요. 실패 후 통합 문서나 PDF에 부분 결과가 남았는지 확인한 뒤 재실행합니다. 실제 운영에 원자적 롤백이 필요하다면 임시 출력 시트·임시 파일을 만든 후 성공할 때 교체하는 별도 설계가 필요합니다.

기존 PDF 경로 확인의 한계

본문의 Dir$ 검사는 일반 파일뿐 아니라 읽기 전용·숨김·시스템 파일과 디렉터리 속성을 명시해 같은 경로의 기존 항목을 확인합니다. Microsoft Dir 문서의 속성 정의를 근거로 보완했으며, Windows Excel에서 이 경계를 실제 실행한 기록은 없습니다. 검사 직후 다른 프로그램이 같은 경로를 만드는 경쟁 상태까지 잠그지는 못하므로 단독 연습 폴더에서 사본으로 시험하세요.

7. 운영 한계와 외부 데이터 새로 고침

ThisWorkbook.RefreshAll을 호출한 직후 다음 줄에서 피벗이나 PDF를 생성하면 데이터가 아직 갱신 중일 수 있습니다. Microsoft 문서도 BackgroundQuery=True인 개체는 백그라운드에서 갱신된다고 설명합니다. 따라서 이 예제에는 무조건적인 RefreshAll 호출을 넣지 않았습니다.

외부 연결을 붙이려면 해당 연결이 지원하는 동기 실행 방식, 완료·실패 판정, 인증 프롬프트와 시간 제한을 별도로 구현하고 테스트해야 합니다. 단순히 몇 초 기다리는 코드를 추가하거나 모든 연결이 같은 방식으로 끝난다고 가정하지 마세요. 데이터가 준비된 것을 확인한 뒤 이 보고서 매크로를 실행하는 방식부터 시작할 수 있습니다.

대용량 데이터, 데이터 모델·OLAP 피벗, 공유 캐시, 여러 사람이 동시에 쓰는 파일은 별도 설계가 필요합니다. 데이터량과 실행 환경을 실제로 측정한 뒤 처리 방식을 선택하세요. 이 글은 특정 데이터량의 속도를 측정한 벤치마크가 아닙니다.

8. 결론 및 다음 행동

먼저 10행의 연습 데이터와 전용 분석 시트로 입력·집계·PDF를 대조하세요. 그다음 업무 데이터의 머리글과 집계 기준을 맞추고, 외부 새로 고침이나 추가 작업은 단계별로 붙입니다. 핵심은 호출 순서뿐 아니라 실패 시 중단 지점과 복원 범위를 명확히 하는 것입니다.

공식 문서

• Workbook.RefreshAll: 외부 데이터와 피벗 새로 고침 및 백그라운드 실행
• PivotCaches.Create: 캐시 생성과 원본 범위 지정
• PivotCache.CreatePivotTable: 피벗 생성과 필드 배치 예
• PivotTable.AddDataField: 집계 필드 추가
• PivotTable.ClearTable: 필드·필터·정렬 초기화
• PivotTable.ChangePivotCache: 일반 워크시트 원본의 캐시 변경
• Worksheet.ExportAsFixedFormat: 지정한 워크시트 내보내기
• FormatConditions.AddDatabar: 데이터 막대 추가

문서 확인일: 2026-10-01

함께 읽으면 좋은 글

• VBA FileSystemObject — 폴더 생성·파일 복사·백업 자동화 풀 가이드
• 보고서를 버튼 한 번에 PDF로 — VBA ExportAsFixedFormat

작성자 소개

최지원

엑셀·VBA·파이썬·AI 업무 자동화의 사용 절차와 실습 자료를 정리합니다.

WP 게시물 작성자의 글 보기