엑셀 VBA 자동화 (24편): 행 데이터를 한 줄씩 쪼개어 서식 시트에 맞춰 개별 파일로 분리 저장하기

엑셀 VBA 자동화 (24편): 행 데이터를 한 줄씩 쪼개어 서식 시트에 맞춰 개별 파일로 분리 저장하기

인사팀에서 직원별 급여명세서를 발송하거나, 행정실에서 개인별 평가 안내서를 배포할 때 하나의 마스터 명부를 기반으로 '개별 서식'에 값을 하나씩 꽂아 별도 파일로 저장해야 하는 순간이 있습니다. 오늘은 반복적인 수작업 매칭을 타파하고, 마스터 행 데이터를 순회하며 지정된 양식에 값을 자동으로 대입한 뒤 개별 파일로 일괄 도출하는 고급 제어 매크로 프로그램을 구현해 보겠습니다.

VBA AUTOMATION GUIDE

실무 자동화를 진행하다 보면 단순히 시트를 분할하는 수준을 넘어, 미리 정해진 '양식(Form) 시트'에 데이터를 동적으로 매칭해야 하는 고난도 공정이 발생합니다. 예를 들어 가로로 길게 나열된 임직원 100명의 수당 데이터를 세로 형태의 정식 '급여지급 명세서' 서식의 적절한 위치에 자동으로 입력하고, 입력이 끝날 때마다 '홍길동_명세서.xlsx'라는 독립된 워크북 파일로 저장하여 출력하는 작업입니다. 이 메커니즘을 마스터하면 수작업으로 며칠이 걸리던 행정 정산 업무를 수 초 내에 완벽하게 완결할 수 있습니다.

1. 자동화 제어 프로그램 작성을 위한 개발 환경 세팅
이 매크로 프로그램을 구동하기 위해서는 파일 내에 최소 두 개의 시트가 필요합니다. 하나는 데이터가 나열된 "명부" 시트이고, 다른 하나는 멋지게 테두리와 결재란이 그려진 "서식" 시트입니다. 설정을 위해 키보드의 Alt + F11을 눌러 VBA 편집기를 연 뒤, 상단 메뉴의 [삽입(I)] - [모듈(M)]을 클릭하여 코드 창을 개설하고 아래의 마스터 소스코드를 오타 없이 복사하여 붙여넣습니다.

Sub ExportIndividualForms()
    Dim wsData As Worksheet, wsForm As Worksheet
    Dim wbNew As Workbook
    Dim lastRow As Long, i As Long
    Dim empName As String, savePath As String

    Set wsData = ThisWorkbook.Sheets("명부")
    Set wsForm = ThisWorkbook.Sheets("서식")
    savePath = ThisWorkbook.Path & "\" '현재 파일과 같은 폴더에 생성
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False

    ' 2행부터 마지막 직원 행까지 순회 반복 실행
    For i = 2 To lastRow
        empName = wsData.Cells(i, 2).Value 'B열: 직원 이름

        ' 명부의 데이터를 서식 시트의 지정된 셀 위치에 매칭 매립
        wsForm.Range("B4").Value = wsData.Cells(i, 1).Value '사번 -> 서식 B4
        wsForm.Range("D4").Value = empName '성명 -> 서식 D4
        wsForm.Range("B6").Value = wsData.Cells(i, 3).Value '부서 -> 서식 B6
        wsForm.Range("D6").Value = wsData.Cells(i, 4).Value '금액 -> 서식 D6

        ' 값이 채워진 서식 시트만 복사하여 새로운 독립 워크북 파일 개설
        wsForm.Copy
        Set wbNew = ActiveWorkbook

        ' '성명_명세서.xlsx' 파일명으로 자동 저장 후 닫기
        wbNew.SaveAs Filename:=savePath & empName & "_명세서.xlsx", FileFormat:=xlOpenXMLWorkbook
        wbNew.Close SaveChanges:=False
    Next i

    Application.ScreenUpdating = True
    MsgBox "모든 개인별 서식 파일 분리가 완료되었습니다!", vbInformation
End Sub

2. 매크로의 데이터 바인딩 알고리즘 해설
이 프로그램의 핵심 메커니즘은 wsForm.Copy 명령어에 숨어있습니다. 엑셀 워크시트 객체 뒤에 인수를 지정하지 않고 .Copy를 실행하면, 엑셀은 해당 시트의 디자인과 서식을 그대로 복사하여 메모리 상에 완전히 새로운 독립 엑셀 파일(Workbook)을 자동으로 개설해 줍니다. For...Next 루프문이 동작하면서 '명부' 시트의 2행부터 차례대로 데이터를 읽어와 '서식' 시트의 고정된 좌표(B4, D4 등)에 값을 꽂아 넣고, 복사 및 SaveAs 명령어를 통해 가동 폴더 내에 순식간에 독립적인 엑셀 결과물 파일들을 파편화하여 적재하는 고급 자동화 알고리즘입니다.

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

• 수식 참조 주의: 복사 대상인 "서식" 시트 내부에 다른 시트를 참조하는 수식이 걸려있다면, 파일이 분리될 때 링크가 깨져 #REF! 에러가 날 수 있습니다. 서식 시트 내의 연산은 되도록 자체 셀 간의 계산으로만 완결되도록 설계하는 것이 안전합니다.
• 중복 파일명 처리: 동명이인이 있을 경우 파일이 덮어써 질 위험이 있습니다. 이 경우 파일명을 지정하는 코드 부근을 empName & "_" & wsData.Cells(i, 1).Value & "_명세서.xlsx" 처럼 이름 뒤에 사번을 붙이도록 커스텀하면 파일이 손실되는 에러를 방지할 수 있습니다.
• 파일 사전 저장: ThisWorkbook.Path는 현재 매크로 파일이 위치한 경로를 뜻합니다. 새로 만든 빈 문서에 코드를 바로 실행하면 경로를 찾지 못해 에러가 나므로, 반드시 원본 매크로 파일을 하드디스크에 한 번 저장한 뒤 실행해야 합니다.

데이터 시트 구성 및 대입 바인딩 좌표 구조

명부 원본 열 데이터 (A~D열)	서식 시트 대입 목적지 좌표	최종 루프 제어 결과물
A열 : 사원번호 (예: 202601)	"서식" 시트의 B4 셀로 자동 이동	김철수_명세서.xlsx 파일 생성
B열 : 성명 (예: 김철수)	"서식" 시트의 D4 셀로 자동 이동 및 파일명 활용	이영희_명세서.xlsx 파일 생성
C열 : 소속부서 (예: 재무팀)	"서식" 시트의 B6 셀로 자동 이동	박민수_명세서.xlsx 파일 생성
D열 : 지급금액 (예: 5,000,000)	"서식" 시트의 D6 셀로 자동 이동	개별 파일에는 본인 데이터 서식만 존재

매월 돌아오는 개별 서식 발송 업무를 위해 야근을 밥 먹듯 하셨다면 오늘 배운 행 데이터 순회 분리 매크로가 완벽한 해결책이 될 것입니다. 마스터 명부의 규격과 대입할 양식의 위치를 코드로 연결해 주는 이 짧은 스크립트 하나로, 번거로운 복사·붙여넣기 업무 프로세스를 완전히 시스템화하여 업무 정밀도를 100%로 끌어올리고 정시 퇴근의 여유를 쟁취해 보시기 바랍니다.