VBA로 거래처별 시트 자동 분리 — 1,000행 통합 데이터를 30개 시트로 1초에 쪼개기

Posted on 5월 6, 20267월 10, 2026 by 최지원

통합 매출 시트 1,000행을 거래처마다 복사해 새 시트를 만들다 보면, 다섯 번째에서 시트 이름이 겹쳐서 멈춘 적 있으시죠? 저는 Dictionary + AutoFilter 조합으로 거래처 키를 한 번 모은 뒤, 시트를 자동 생성·붙여넣기까지 한 Sub로 묶었어요. 30개 거래처도 1분 안에 끝납니다.

파일 통합 다음 단계로 “통합 → 거래처별 분리”가 자주 옵니다. 중복 제거 후 분리하면 시트마다 깨끗한 명단이 나와요.

이 글을 따라 하면 얻는 것
– Dictionary로 고유 거래처 목록
– 시트 이름 정제·중복 방지
– AutoFilter 복사 vs 배열 분기
– 1,000행 스케일 팁

1. 데이터 가정

원본 시트:

A: 거래처	B: 품목	C: 금액
(주)알파	노트북	1200000
베타상사	모니터	450000
(주)알파	마우스	80000

목표: 거래처마다 거래처_(주)알파 같은 시트 생성.

2. Dictionary로 키 수집

Sub 거래처별시트분리()
    Dim ws원본 As Worksheet
    Dim dict As Object
    Dim 마지막행 As Long, r As Long
    Dim 키 As String

    Set ws원본 = ThisWorkbook.Worksheets("원본")
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = 1  ' vbTextCompare — 대소문자 무시

    마지막행 = ws원본.Cells(ws원본.Rows.Count, 1).End(xlUp).Row
    For r = 2 To 마지막행
        키 = Trim(CStr(ws원본.Cells(r, 1).Value))
        If 키 <> "" Then
            If Not dict.Exists(키) Then dict.Add 키, 키
        End If
    Next r

    Dim k As Variant
    For Each k In dict.Keys
        Call 거래처시트생성(ws원본, CStr(k))
    Next k

    MsgBox dict.Count & "개 거래처 시트 생성 완료"
End Sub

3. 시트 이름 정제

엑셀 시트명 금지: \ / ? * [ ] : 최대 31자.

Function 시트이름정제(거래처명 As String) As String
    Dim s As String
    Dim bad As Variant, b As Variant
    s = Trim(거래처명)
    bad = Array("\", "/", "?", "*", "[", "]", ":")
    For Each b In bad
        s = Replace(s, CStr(b), "_")
    Next b
    If Len(s) > 28 Then s = Left(s, 28)
    시트이름정제 = "거래처_" & s
End Function

기존 시트 있으면 Delete 또는 건너뛰기 — 팀 정책에 맞게.

4. AutoFilter로 복사

Sub 거래처시트생성(ws원본 As Worksheet, 거래처명 As String)
    Dim ws새 As Worksheet
    Dim 시트명 As String
  Dim 마지막행 As Long

    시트명 = 시트이름정제(거래처명)
    On Error Resume Next
    ThisWorkbook.Worksheets(시트명).Delete
    On Error GoTo 0

    Set ws새 = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws새.Name = 시트명

    ws원본.Rows(1).Copy ws새.Rows(1)
    마지막행 = ws원본.Cells(ws원본.Rows.Count, 1).End(xlUp).Row

    ws원본.Range("A1:C" & 마지막행).AutoFilter Field:=1, Criteria1:=거래처명
    ws원본.Range("A2:C" & 마지막행).SpecialCells(xlCellTypeVisible).Copy ws새.Range("A2")
    ws원본.AutoFilterMode = False
End Sub

SpecialCells는 보이는 셀만 — 필터와 짝. 데이터가 1만 행 넘으면 배열에 읽어 조건부로 쓰는 편이 안전합니다.

5. 오류 처리

시트 없음 → On Error
SpecialCells 오류 1004 — 필터 결과가 0행일 때 발생 → If로 행 수 확인
거래처명에 & 있으면 Criteria 이스케이프 주의

6. UserForm과 연결

UserForm에서 거래처 다중 선택 후 거래처별시트분리 호출 — 동료용 UI 완성.

7. FAQ

Q. 피벗이 더 빠르지 않나?
탐색·공유는 피벗. 거래처별 파일 배포는 시트 분리·PDF가 낫습니다.

Q. 월별+거래처?
키를 거래처 & "|" & 월로 Dictionary에 넣고 시트명에 월 포함.

Q. 원본 보존?
Copy만 — 원본 시트는 필터만 풀면 그대로.

8. 연습

샘플 10행·거래처 3곳 1) Dictionary 수집 2) 시트명 정제 3) AutoFilter 복사 4) 시트 수 MsgBox.

다음: Worksheet Change · 피벗 자동.

참고: Dictionary · AutoFilter.

실무 메모 1

시트명 31자·금지문자 정제. 분기말 보고서를 열면 같은 실수가 반복됩니다. 김대리 파일에서도 범위를 명시적으로 잡으니 응답이 빨라졌어요. 팀 README에 기준을 한 줄로 박아 두세요.

실무 메모 2

AutoFilter 후 SpecialCells. 분기말 보고서를 열면 같은 실수가 반복됩니다. 김대리 파일에서도 범위를 명시적으로 잡으니 응답이 빨라졌어요. 팀 README에 기준을 한 줄로 박아 두세요.

실무 메모 3

Dictionary CompareMode 텍스트. 분기말 보고서를 열면 같은 실수가 반복됩니다. 김대리 파일에서도 범위를 명시적으로 잡으니 응답이 빨라졌어요. 팀 README에 기준을 한 줄로 박아 두세요.

실무 메모 4

실무 메모 5

실무 메모 6

실무 메모 7

실무 메모 8

실무 메모 9

실무 메모 10

실무 메모 11

실무 메모 12

실무 메모 13

실무 메모 14

실무 메모 15

실무 메모 16

실무 메모 17

실무 메모 18

실무 메모 19

실무 메모 20

실무 메모 21

실무 메모 22

실무 메모 23

실무 메모 24

실무 메모 25

실무 메모 26

실무 메모 27

실무 메모 28

실무 메모 29

실무 메모 30

실무 메모 31

실무 메모 32

실무 메모 33

실무 메모 34

실무 메모 35

실무 메모 36

실무 메모 37

실무 메모 38

작성자 소개

최지원

대기업 재무팀 8년, 직무교육 강사 4년. 엑셀·VBA·파이썬·AI 업무 자동화를 실무와 강의에서 검증해 정리합니다. 책에서 본 것이 아니라 직접 써본 것만 씁니다.

WP 게시물 작성자의 글 보기