엑셀 VBA 자동화 (23편): 버튼 하나로 마스터 데이터에서 부서별 독립 엑셀 파일 일괄 분리 저장하기

엑셀 VBA 자동화 (23편): 버튼 하나로 마스터 데이터에서 부서별 독립 엑셀 파일 일괄 분리 저장하기

사내 전체 임직원 명부나 종합 지출 내역서에서 각 부서장에게 전달할 '부서별 추출 파일'을 각각 따로 만들어 저장해야 할 때가 있습니다. 필터를 걸고, 새 통합 문서를 열고, 복사하고, 부서명으로 저장하는 소모적인 수작업을 반복하고 계셨다면 오늘 가이드를 주목해 주십시오. 마스터 시트의 데이터를 읽어 부서별 고유 파일(*.xlsx)을 단 1초 만에 폴더에 쏟아내는 매크로 프로그램을 제작해 보겠습니다.

VBA AUTOMATION GUIDE

인사팀이나 재무팀의 행정 실무 중 가장 빈번하면서도 귀찮은 작업이 바로 '데이터 파편화 및 배포'입니다. 하나의 커다란 마스터 시트 데이터를 조건에 맞게 쪼개어 각각 독립된 파일로 저장하는 업무 말입니다. 이 작업을 수동으로 하면 부서가 20개일 때 똑같은 복사 및 저장 행위를 20번 반복해야 하며, 그 과정에서 텍스트가 누락되거나 다른 부서의 대외비 정보가 섞여 들어가는 치명적인 휴먼 에러가 발생할 수 있습니다. 엑셀 VBA의 고급 필터(AdvancedFilter) 메커니즘과 워크북 저장(SaveAs) 명령어를 결합하면 이러한 공정을 완벽하게 시스템화할 수 있습니다.

1. 매크로 작성을 위한 클릭 단계 및 환경 세팅
자동화 코드를 삽입하기 위해 엑셀 창을 활성화한 상태에서 키보드의 Alt + F11을 동시에 눌러 Visual Basic for Applications(VBA) 개발자 편집기 창을 호출합니다. 좌측 프로젝트 탐색기 빈 영역에서 마우스 우클릭을 한 후 [삽입(I)] - [모듈(M)]을 차례대로 선택하여 코드를 작성할 비어 있는 모듈 시트를 생성합니다. 아래의 자동화 프로그램 원본 코드를 드래그하여 빠짐없이 복사한 뒤 모듈 창에 붙여넣습니다.

Sub SplitAndSaveAsNewFiles()
    Dim mainSheet As Worksheet
    Dim uniqueDept As New Collection
    Dim cell As Range, i As Long
    Dim lastRow As Long, deptName As Variant
    Dim newWorkbook As Workbook
    Dim savePath As String

    Set mainSheet = ThisWorkbook.Sheets("마스터데이터")
    savePath = ThisWorkbook.Path & "\" '현재 파일이 있는 폴더에 저장
    lastRow = mainSheet.Cells(mainSheet.Rows.Count, "A").End(xlUp).Row

    ' 1. B열(부서명)에서 중복 없는 고유 부서 리스트 추출
    On Error Resume Next
    For i = 2 To lastRow
        uniqueDept.Add mainSheet.Cells(i, 2).Value, CStr(mainSheet.Cells(i, 2).Value)
    Next i
    On Error GoTo 0

    ' 2. 부서별로 순회하며 독립 파일 생성 및 저장
    Application.ScreenUpdating = False
    For Each deptName In uniqueDept
        If deptName <> "" Then
            ' 새 엑셀 파일(워크북) 개설
            Set newWorkbook = Workbooks.Add
            mainSheet.Range("A1").AutoFilter Field:=2, Criteria1:=deptName

            ' 필터링된 데이터만 새 파일의 첫 번째 시트에 복사
            mainSheet.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
                newWorkbook.Sheets(1).Range("A1")

            ' 파일명을 '부서명.xlsx'로 지정하여 저장 후 닫기
            newWorkbook.SaveAs Filename:=savePath & deptName & ".xlsx", FileFormat:=xlOpenXMLWorkbook
            newWorkbook.Close SaveChanges:=False
        End If
    Next deptName

    mainSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
    MsgBox "모든 부서별 파일 분리 저장이 완료되었습니다!", vbInformation
End Sub

2. 자동화 핵심 명령어 및 알고리즘 해설
코드의 첫 단계인 Collection 객체는 중복을 허용하지 않는 특성을 가집니다. 이를 이용해 마스터 데이터 B열에 적힌 수많은 부서명 중 '인사팀', '재무팀', '행정팀' 등 고유한 이름만 쏙 골라내어 기억창고에 담습니다. 이후 For Each 문을 통해 이 부서 리스트를 한 바퀴 돌면서 컴퓨터 내부적으로 Workbooks.Add 명령어명령을 내려 텅 빈 새 엑셀 도화지를 한 장씩 엽니다. 마스터 시트에서 해당 부서명으로 자동 필터를 실행한 결과물 중 xlCellTypeVisible(화면에 보이는 셀)만 추출하여 새 도화지에 복사하고, SaveAs 구문으로 부서 이름을 파일명으로 지정해 실제 하드디스크 폴더에 물리적 파일로 저장하는 정교한 시퀀스입니다.

💡 실수를 방지하는 핵심 팁 및 주의사항

• 시트 이름 매칭 일치: 코드 8행에 명시된 ThisWorkbook.Sheets("마스터데이터") 구문의 큰따옴표 안 문자열은 실제 엑셀 파일 하단의 시트 이름과 토씨 하나 틀리지 않고 완벽하게 일치해야 Subscript out of range 에러를 원천 차단할 수 있습니다.
• ScreenUpdating 옵션: 데이터 분리 작업 중 수십 개의 파일이 열리고 닫히는 화면 깜빡임을 제어하기 위해 ScreenUpdating = False 처리를 해두었습니다. 작업 속도를 수십 배 향상시키는 핵심 팁입니다.
• FileFormat 인수의 의미: xlOpenXMLWorkbook 상수는 매크로 코드가 포함되지 않은 순수한 일반 엑셀 파일 표준 형식인 .xlsx 파일로 저장하겠다는 특성 정의입니다. 배운 코드를 실행하기 전 반드시 원본 매크로 파일을 먼저 저장해 경로를 활성화하십시오.

마스터 시트 구조 및 분리 저장 프로세스 예시

사원번호 (A열)	부서명 (B열 - 필터 기준열)	성명 (C열)	직급 (D열)	매크로 가동 후 폴더 생성 파일 목록
HR-0125	인사팀	김철수	과장	인사팀.xlsx (인사팀 데이터만 포함)
FI-0482	재무팀	이영희	대리	재무팀.xlsx (재무팀 데이터만 포함)
AD-0911	행정팀	박민수	사원	행정팀.xlsx (행정팀 데이터만 포함)

매월 마스터 데이터를 부서별로 쪼개어 전달하는 작업은 행정 관리자에게 무척 지루하고 손이 많이 가는 공정입니다. 오늘 구현한 워크북 생성 및 일괄 분리 저장 매크로 기술을 활용하면, 데이터가 수만 행에 달하더라도 마우스 클릭 단 한 번에 완벽히 격리된 보안 파일을 부서별로 도출할 수 있습니다. 수작업에 들였던 에너지를 절약하여 데이터가 담고 있는 실질적인 성과 지표를 분석하는 데 투자해 보십시오.