[Excel/VBA] Excel VBA로 여러 시트 합치기: 코드 분석과 설명

 

 

Excel VBA로 여러 시트 합치기: 코드 분석과 설명

 

Excel에서 여러 개의 시트를 하나의 시트로 합치는 작업은 반복적이고 시간이 많이 소요될 수 있습니다. 이를 자동화하기 위해 VBA 매크로를 사용하면 효율적으로 작업을 수행할 수 있습니다. 이번 글에서는 여러 시트를 시트 정렬 순서대로 하나의 시트로 합치는 VBA 코드를 파트별로 나누어 상세히 설명하겠습니다.

 

 

전체 코드

 

우선, 전체 코드를 살펴보겠습니다.

 

Sub MergeSheets()
    Dim ws As Worksheet
    Dim masterSheet As Worksheet
    Dim lastRow As Long
    Dim firstSheet As Boolean

    Application.ScreenUpdating = False

    ' 기존에 "MergedSheet"라는 시트가 있으면 삭제
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("MergedSheet").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' 새로운 시트 생성
    Set masterSheet = Worksheets.Add(Before:=Worksheets(1))
    masterSheet.Name = "MergedSheet"

    firstSheet = True

    ' 모든 시트를 순회
    For Each ws In ThisWorkbook.Worksheets
        ' 합칠 시트를 "MergedSheet"에서 제외
        If ws.Name <> masterSheet.Name Then
            With ws
                lastRow = masterSheet.Cells(masterSheet.Rows.Count, "A").End(xlUp).Row
                If firstSheet Then
                    ' 첫 번째 시트의 헤더와 모든 데이터를 복사
                    .UsedRange.Copy masterSheet.Cells(1, 1)
                    firstSheet = False
                Else
                    ' 첫 번째 시트 이후로는 헤더를 제외하고 데이터만 복사
                    .UsedRange.Offset(1, 0).Resize(.UsedRange.Rows.Count - 1, .UsedRange.Columns.Count).Copy _
                        masterSheet.Cells(lastRow + 1, 1)
                End If
            End With
        End If
    Next ws

    Application.ScreenUpdating = True

    MsgBox "모든 시트가 'MergedSheet'에 병합되었습니다.", vbInformation
End Sub

 

 

파트별 설명

 

코드를 이해하기 쉽게 여러 파트로 나누어 설명하겠습니다.

 

파트 1: 서브루틴 및 변수 선언

 

Sub MergeSheets()
    Dim ws As Worksheet
    Dim masterSheet As Worksheet
    Dim lastRow As Long
    Dim firstSheet As Boolean

 

설명:

• Sub MergeSheets()는 매크로의 시작을 알리는 선언입니다.

• 변수들을 선언하여 매크로에서 사용할 워크시트와 행 번호 등을 저장합니다.

• ws: 순회할 각 워크시트를 저장하는 변수입니다.

• masterSheet: 병합된 데이터를 담을 새로운 시트를 가리킵니다.

• lastRow: 데이터를 복사할 때 사용할 마지막 행 번호입니다.

• firstSheet: 첫 번째 시트인지 여부를 판단하는 불리언 변수입니다.

 

 

파트 2: 화면 업데이트 중지

    Application.ScreenUpdating = False

 

설명:

• 매크로 실행 속도를 높이고 화면 깜빡임을 방지하기 위해 화면 업데이트를 일시적으로 중지합니다.

 

 

 

파트 3: 기존의 “MergedSheet” 시트 삭제

 

    ' 기존에 "MergedSheet"라는 시트가 있으면 삭제
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("MergedSheet").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

 

설명:

• 에러 무시 설정: On Error Resume Next를 사용하여 오류가 발생해도 매크로가 중단되지 않도록 합니다.

• 삭제 확인 메시지 비활성화: Application.DisplayAlerts = False로 설정하여 시트를 삭제할 때 나타나는 경고 메시지를 숨깁니다.

• 기존 시트 삭제: Worksheets("MergedSheet").Delete를 통해 기존의 MergedSheet를 삭제합니다.

• 삭제 확인 메시지 재활성화 및 에러 처리 복원:

• Application.DisplayAlerts = True로 경고 메시지를 다시 활성화합니다.

• On Error GoTo 0으로 에러 처리를 기본 상태로 복원합니다.

 

 

파트 4: 새로운 “MergedSheet” 시트 생성

    ' 새로운 시트 생성
    Set masterSheet = Worksheets.Add(Before:=Worksheets(1))
    masterSheet.Name = "MergedSheet"

    firstSheet = True

 

설명:

• 새로운 시트 추가: Worksheets.Add(Before:=Worksheets(1))를 사용하여 첫 번째 시트 앞에 새로운 시트를 생성합니다.

• 시트 이름 지정: 생성한 시트의 이름을 MergedSheet로 설정합니다.

• 첫 번째 시트 플래그 초기화: firstSheet 변수를 True로 설정하여 첫 번째 시트를 처리할 때 사용합니다.

 

 

파트 5: 모든 시트를 순회하며 데이터 병합

 

    ' 모든 시트를 순회
    For Each ws In ThisWorkbook.Worksheets
        ' 합칠 시트를 "MergedSheet"에서 제외
        If ws.Name <> masterSheet.Name Then
            With ws
                lastRow = masterSheet.Cells(masterSheet.Rows.Count, "A").End(xlUp).Row
                If firstSheet Then
                    ' 첫 번째 시트의 헤더와 모든 데이터를 복사
                    .UsedRange.Copy masterSheet.Cells(1, 1)
                    firstSheet = False
                Else
                    ' 첫 번째 시트 이후로는 헤더를 제외하고 데이터만 복사
                    .UsedRange.Offset(1, 0).Resize(.UsedRange.Rows.Count - 1, .UsedRange.Columns.Count).Copy _
                        masterSheet.Cells(lastRow + 1, 1)
                End If
            End With
        End If
    Next ws

 

설명:

• 시트 순회 시작: For Each ws In ThisWorkbook.Worksheets를 사용하여 현재 워크북의 모든 시트를 순회합니다.

• “MergedSheet” 시트 제외: If ws.Name <> masterSheet.Name 조건문을 통해 병합 대상에서 MergedSheet 시트를 제외합니다.

• 데이터 복사 및 붙여넣기:

• 마지막 행 찾기: lastRow 변수에 MergedSheet의 현재 마지막 행 번호를 저장합니다.

• 첫 번째 시트 처리:

• firstSheet 변수가 True인 경우, 첫 번째 시트로 간주합니다.

• .UsedRange.Copy masterSheet.Cells(1, 1)를 사용하여 해당 시트의 모든 데이터(헤더 포함)를 MergedSheet의 첫 번째 셀부터 복사합니다.

• firstSheet 변수를 False로 변경하여 이후 시트는 첫 번째 시트로 처리되지 않도록 합니다.

• 이후 시트 처리:

• .UsedRange.Offset(1, 0)를 사용하여 첫 번째 행(헤더)을 제외한 데이터 범위를 지정합니다.

• .Resize(.UsedRange.Rows.Count - 1, .UsedRange.Columns.Count)로 데이터 범위의 크기를 조정합니다.

• 지정된 데이터 범위를 masterSheet.Cells(lastRow + 1, 1) 위치에 복사하여 붙여넣습니다.

 

 

파트 6: 화면 업데이트 재개 및 완료 메시지 표시

    Application.ScreenUpdating = True

    MsgBox "모든 시트가 'MergedSheet'에 병합되었습니다.", vbInformation
End Sub

 

설명:

• 화면 업데이트 재개: Application.ScreenUpdating = True로 설정하여 화면 업데이트를 다시 활성화합니다.

• 완료 메시지 표시: MsgBox 함수를 사용하여 사용자에게 매크로 실행이 완료되었음을 알립니다.

 

 

결론

 

이 매크로는 Excel 워크북 내의 모든 시트를 순회하여 데이터를 하나의 시트로 병합하는 기능을 수행합니다. 첫 번째 시트의 헤더를 포함한 모든 데이터를 복사하고, 이후 시트들의 데이터는 헤더를 제외하고 순차적으로 아래에 붙여넣습니다. 이를 통해 여러 시트의 데이터를 효율적으로 하나의 시트로 통합할 수 있습니다.

 

 

추가 팁

 

• 특정 시트 제외하기: 만약 특정 시트를 병합 대상에서 제외하고 싶다면, 조건문에 해당 시트의 이름을 추가하여 제외할 수 있습니다.

 

If ws.Name <> masterSheet.Name And ws.Name <> "제외할시트명" Then

 

• 시트 이름 변경: 생성되는 병합 시트의 이름을 변경하고 싶다면, masterSheet.Name = "MergedSheet" 부분을 수정하여 원하는 이름으로 변경하세요.

• 데이터 일관성 확인: 모든 시트의 데이터 구조(열 순서와 데이터 형식)가 동일해야 올바르게 병합됩니다.

• 백업 권장: 매크로 실행 전에 워크북을 백업하여 데이터 손실에 대비하세요.

 

 

 

매크로 사용 방법

 

1. VBA 편집기 열기: • Excel에서 Alt + F11 키를 눌러 VBA 편집기를 엽니다.
2. 모듈 추가: • 메뉴에서 삽입 > 모듈을 선택하여 새로운 모듈을 추가합니다.
3. 코드 붙여넣기: • 생성된 모듈 창에 제공된 코드를 복사하여 붙여넣습니다.
4. 매크로 실행: 
• VBA 편집기를 닫고 Excel로 돌아와 Alt + F8 키를 눌러 매크로 목록을 엽니다.
• MergeSheets 매크로를 선택하고 실행 버튼을 클릭합니다.