엑셀 VBA 시트 합치기 프로그램 :: 시트 병합 매크로 총정리

엑셀 VBA로 시트 병합을 할 수 있는 마스터 코드의 동작 원리와 명령문을 사용한 시트 합치기 툴 제작방법을 단계별로 알아봅니다.

홈페이지 » 엑셀 VBA 시트 합치기 프로그램 :: 시트 병합 매크로 총정리

엑셀 시트 합치기 프로그램 만들기 :: 퀵 VBA 2강

엑셀 시트 합치기 프로그램 목차 바로가기
강의 요약

엑셀은 아주 훌륭한 프로그램이고 현재 공유기능을 지원하고 있지만, 그럼에도 불구하고, 아직까지는 많은 사람이 동시에 같은 자료를 관리하기에 다소 부족한 부분이 있습니다. 그러다보니, 실제 현업에서는 동일한 형태의 시트를 각 부서 또는 각 매장별로 나눠서 관리하는 경우가 많은데요. 이번 강의에서는 이렇게 여러개로 나뉘어진 시트 중에 원하는 시트만 선택해서 하나로 합치는 시트 합치기 프로그램 제작 방법을 단계별로 알아보겠습니다.

엑셀 시트 합치기 프로그램 예제
시트 합치기 프로그램 제작 방법을 단계별로 알아봅니다.
영상강의

예제파일 다운로드

1. 강의에 사용된 전체 명령문

1-A. 유저폼 명령문
Option Explicit
 
Private Sub btnSubmit_Click()
 
Dim WB As Workbook
Dim WS As Worksheet: Dim newWS As Worksheet
Dim Rng As Range
Dim i As Long: Dim j As Long: j = 2
Dim endCol As Long: Dim endRow As Long
 
Set WB = ThisWorkbook
 
'// isListBoxSelected 함수에 대한 자세한 설명은 아래 링크를 참고하세요.
'// http://www.oppadu.com/엑셀-사전/vba-리스트박스-값-선택여부/
If isListBoxSelected(Me.lstSheet) = False Then MsgBox "시트를 선택하세요.": Exit Sub
 
For Each WS In WB.Worksheets
    If WS.Name = "시트병합" Then MsgBox "시트병합 시트가 존재합니다. 시트병합 시트의 이름을 변경하시거나 삭제하신 뒤 다시 실행해주세요.": Exit Sub
Next
 
Set newWS = WB.Worksheets.Add(before:=WB.Worksheets(1))
newWS.Name = "시트병합"
With newWS
    .Cells(1, 1) = "매장"
    .Cells(1, 2) = "날짜"
    .Cells(1, 3) = "이름"
    .Cells(1, 4) = "출근시간"
    .Cells(1, 5) = "퇴근시간"
End With
 
For i = 0 To Me.lstSheet.ListCount - 1
    If Me.lstSheet.Selected(i) = True Then
        Set WS = WB.Worksheets(Me.lstSheet.List(i))
        With WS
            '// 시트의 마지막 행/열 받아오기에 대한 자세한 설명은 아래 링크를 참고하세요.
            '// http://www.oppadu.com/엑셀-사전/엑셀-vba-마지막-셀-찾기-마지막-행-찾기/
            endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
            Set Rng = .Range(.Cells(2, 1), .Cells(endRow, endCol))
            Rng.Copy newWS.Cells(j, 1)
            j = j + Rng.Rows.Count
        End With
    End If
Next
 
MsgBox "시트 병합이 완료되었습니다."
Unload Me
 
End Sub
 
Private Sub UserForm_Initialize()
 
Dim WB As Workbook
Dim WS As Worksheet
 
Set WB = ThisWorkbook
 
For Each WS In WB.Worksheets
    Me.lstSheet.AddItem WS.Name
Next
 
End Sub
 
Function isListBoxSelected(ListBox As MSForms.ListBox) As Boolean
 
Dim i As Long
 
For i = 0 To ListBox.ListCount - 1
If ListBox.Selected(i) Then isListBoxSelected = True: Exit Function
Next
 
isListBoxSelected = False
 
End Function
1-B. 모듈 명령문
Option Explicit
 
Sub Merge_sheet()
 
frmShtSelection.Show
 
End Sub

2. 사용자 정의 폼(유저폼) 추가하기

예제파일을 실행합니다. 화면 상단의 '개발도구' - 'Visual Basic' 를 클릭하거나, 단축키 Alt + F11 키로 VBA 편집기를 실행합니다. 이후 '삽입' - '사용자 정의 폼'을 추가한 뒤, 아래와 같이 각 컨트롤을 추가합니다.

만약 엑셀 화면에 개발도구가 보이지 않을경우, 아래 개발도구 활성화하기 관련 포스트를 확인하세요.

1. 사용자 정의 폼 설정
원하는 시트를 선택후 병합하기 위해 사용자 정의폼을 생성합니다.

3. 유저폼 초기화 명령문 작성하기 (Userform_Initialize)

유저폼 최초 실행 시 동작할 명령문을 작성합니다. 유저폼이 실행되면, '현재 통합문서에 존재하는 모든 시트목록을 리스트박스에 출력' 되도록 유저폼 모듈 안에 아래 명령문을 복사하여 붙여넣기 합니다.

Private Sub UserForm_Initialize()
 
Dim WB As Workbook
Dim WS As Worksheet
 
Set WB = ThisWorkbook
 
For Each WS In WB.Worksheets
    Me.lstSheet.AddItem WS.Name
Next
 
End Sub

4. 시트 합치기 VBA 명령문 작성하기

4-A. 시트 합치기 명령문 변수 설정

아래와 같이 변수를 설정합니다.

변수설명
WB, WS사용중인 통합문서와 내용을 병합할 워크시트입니다.
Rng각 시트에서 병합을 위해 붙여넣기 할 대상 범위입니다.
newWS시트 병합을 위하여 새로 생성되는 워크시트입니다.
iFor 문 / For Each문을 반복을 위한 정수값입니다.
j새로운 워크시트에 붙여넣기 할 시작셀 행번호입니다.
endCol, endRow각 시트의 마지막 행/열 번호입니다.
Dim WB As Workbook
Dim WS As Worksheet: Dim newWS As Worksheet
Dim Rng As Range
Dim i As Long: Dim j As Long: j = 2
Dim endCol As Long: Dim endRow As Long
 
Set WB = ThisWorkbook
$-B. 오류 처리

본격적으로 시트 합치기 명령문을 실행하기 전, 오류 방지를 위해 아래 2가지 오류를 처리구문을 작성합니다.

  1. 리스트박스에서 아무런 항목도 선택되지 않은 채 사용자가 버튼을 클릭했을 경우
  2. 기존 ‘시트병합’ 시트가 존재할 경우
'// isListBoxSelected 함수에 대한 자세한 설명은 아래 링크를 참고하세요.
'// http://www.oppadu.com/엑셀-사전/vba-리스트박스-값-선택여부/
If isListBoxSelected(Me.lstSheet) = False Then MsgBox "시트를 선택하세요.": Exit Sub
 
For Each WS In WB.Worksheets
    If WS.Name = "시트병합" Then MsgBox "시트병합 시트가 존재합니다. 시트병합 시트의 이름을 변경하시거나 삭제하신 뒤 다시 실행해주세요.": Exit Sub
Next
4-C. 새로운 워크시트 생성 및 머리글 입력

각 시트의 내용을 병합할 새로운 워크시트를 생성한 뒤, 머리글을 입력합니다.

Set newWS = WB.Worksheets.Add(before:=WB.Worksheets(1))
newWS.Name = "시트병합"
With newWS
    .Cells(1, 1) = "매장"
    .Cells(1, 2) = "날짜"
    .Cells(1, 3) = "이름"
    .Cells(1, 4) = "출근시간"
    .Cells(1, 5) = "퇴근시간"
End With
4-D. 각 시트를 돌아가며 '시트병합' 시트에 내용 합치기

리스트박스의 항목을 하나씩 돌려가며, 선택된 시트를 확인합니다. 선택된 시트일 경우, 해당 시트에서 마지막 행(endRow)과 마지막 열(endCol) 번호를 받아와 시트의 마지막 셀 위치를 확인합니다. (Set Rng = .Range(.Cells(2, 1), .Cells(endRow, endCol)))

이후 복사/붙여넣기 할 범위를 지정한 뒤 새로 생성한 ‘시트병합’ 시트에 내용을 붙여넣기 합니다. (Rng.Copy newWS.Cells(j, 1))

복사/붙여넣기를 완로하였으면, 시트병합 시트에 새롭게 붙여넣기 할 열번호(j)를 업데이트 한 뒤, 다음 선택된 항목으로 이동합니다.

isListBoxSelected 함수 및 시트의 마지막 행/열 번호 찾기에 대한 자세한 설명은 아래 관련 링크를 확인하세요.

For i = 0 To Me.lstSheet.ListCount - 1
    If Me.lstSheet.Selected(i) = True Then
        Set WS = WB.Worksheets(Me.lstSheet.List(i))
        With WS
            '// 시트의 마지막 행/열 받아오기에 대한 자세한 설명은 아래 링크를 참고하세요.
            '// http://www.oppadu.com/엑셀-사전/엑셀-vba-마지막-셀-찾기-마지막-행-찾기/
            endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
            Set Rng = .Range(.Cells(2, 1), .Cells(endRow, endCol))
            Rng.Copy newWS.Cells(j, 1)
            j = j + Rng.Rows.Count
        End With
    End If
Next
4-E. 시트 합치기 완료 안내메세지 출력

시트 병합이 완료되었다는 안내메세지를 출력한 뒤, 유저폼을 종료합니다.

MsgBox "시트 병합이 완료되었습니다."
Unload Me
4-F. 보조 함수/명령문 입력

위 명령문에 사용된 isListBoxSelected 함수를 추가합니다. 해당 함수 및 명령문에 대한 자세한 설명은 관련 포스트를 참고하세요.

Function isListBoxSelected(ListBox As MSForms.ListBox) As Boolean
 
Dim i As Long
 
For i = 0 To ListBox.ListCount - 1
If ListBox.Selected(i) Then isListBoxSelected = True: Exit Function
Next
 
isListBoxSelected = False
 
End Function

5. 유저폼 불러오기 단축키 설정

유저폼을 더욱 쉽게 불러올 수 있도록 단축키를 설정합니다. VBA 편집기에서 [삽입] – [모듈] 로 새로운 모듈을 추가한 뒤, 아래 명령문을 복사하여 붙여넣기 합니다.

Sub Merge_sheet()
 
frmShtSelection.Show
 
End Sub

이후 엑셀 시트로 돌아온 뒤, 상단의 [개발도구] – [매크로] 를 클릭하면, 방금 생성한 ‘Merge_sheet’ 명령문이 있습니다. 'Merge_sheet' 명령문을 선택한 뒤, [옵션]을 클릭합니다.

개발도구 매크로 옵션
'개발도구' - '매크로'를 선택한 뒤, Merge_Sheet - '옵션'을 클릭합니다.

이후 유저폼을 불러오기 위하여 원하는 단축키로 설정 한 뒤, '확인'을 눌러 시트 합치기 프로그램 제작을 마무리 합니다. 이제 단축키를 입력하면 유저폼이 출력되며, 원하는 시트를 선택하여 병합할 수 있습니다.

시트 합치기 모듈 단축키
유저폼을 불러오기 위하여 원하는 단축키를 설정 후 '확인'을 눌러 마무리합니다.
5 9 votes
별점주기
현재 페이지 댓글알림 신청
알림 설정
guest
21 Comments
Inline Feedbacks
View all comments
김희준
김희준
2019년 12월 24일 3:12 오후

시트 병합이후 병합된 시트내에서의 특정열의 중복확인을 하고 싶습니다.
VBA 명령문을 어떻게 작성해야 할까요?

오빠두엑셀
2019년 12월 25일 5:57 오전
Reply to  김희준

안녕하세요? 오빠두엑셀이에요 ^_^* 예를들어, 시트 A + 시트 B 를 병합한 뒤 'A열'에 중복된 데이터가 있는지 확인을 하고 싶으신건가요?:) 반환되는 동작에 따라 달라집니다. 예를 들어, 중복된 값이 있을 경우 중복된… 더보기 »

김희준
김희준
2019년 12월 26일 1:16 오후

감사합니다! 첨부해주신 명령문을 Private Sub btnSubmit_Click() 끝에 추가하니, 잘 작동되는것을 확인 할 수 있었습니다. 추가로 반환되는 동작을 변경하고 싶어서 댓글 남깁니다. 반환되는 동작을 특정열에서 중복확인 후 중복되는 값이 있다면, 새로운… 더보기 »

김훈
김훈
2020년 1월 23일 11:17 오전
별점주기 :
     

안녕하세요 늘 좋은강의 감사합니다 🙂
다름이 아니라 혹시 완성파일도 받아볼 수 있을까 해서 문의 남깁니다 ㅎㅎ
완성파일과 예제랑 비교하면서 하나씩 따라해보면 좋을 것 같아서요!!

하늬돌이
하늬돌이
2020년 2월 12일 1:48 오전
별점주기 :
     

좋은 자료 감사해요. 잘 활용하고 있습니다.

바늘2
바늘2
2020년 3월 31일 8:40 오후
별점주기 :
     

시트병합시 서식없이 값만 복사하고 싶으면 어디를 수정하면 되는지 알려주시면 감사하겠습니다. range.copy가 아닌 것을 써야 하는지... 부탁 드립니다.

약돌한우
약돌한우
2020년 4월 15일 12:26 오전
별점주기 :
     

인터넷에서 파일병합프로그램 찾아썼던 기억이 있는데, 이런것도 vba로 가능하다니 놀라워요. 좋은 정보 고맙습니다!!

이상호
이상호
2020년 4월 24일 5:58 오후

별점주기 :       동영상 감사히 잘 봤습니다. 많은 도움이 되었습니다. 파일 병합시 두가지 문제가 해결되지 않아서 요청드립니다. 1. 전 강의에서 알려주신것처럼 1번행에 특정 레이블을 넣고 싶은데, 어느곳에 어느 코드를 넣어야… 더보기 »

이상호
이상호
2020년 4월 25일 11:32 오전

주말에도 답변 달아주셔서 정말 감사합니다~ 코드 까막눈에게 큰 빛이 되어 주셨습니다~ 다시한번 해보겠습니다~ 감사합니다

이은채
이은채
2020년 4월 27일 5:02 오후
별점주기 :
     

강의 너무너무 잘 보고있습니다. 한가지 추가로 질문드릴것이 있어서 댓글 남깁니다.
시트 붙여넣기 후 중복된 매장이름을 셀 병합하고싶은데 VBA 명령문을 어떻게 작성해야 할까요 MERGE를 사용하고있는데 잘 되지 않아서요ㅜ.ㅜ

손영식
손영식
2020년 5월 8일 3:36 오후
별점주기 :
     

원하는 개수 만큼 시트 전체를 복사하는 VBA명령문은 어떻게 작성해야 할까요?

손영식
손영식
2020년 5월 10일 11:06 오후

정말 감사합니다... 늘 큰도움 받고갑니다. 번창하세요!!

김주호
김주호
2020년 5월 11일 4:22 오후
별점주기 :
     

안녕하세요
별도의 시트를 만들고, 그 시트내에 다른 시트들의 3번째 행부터 붙여넣기 하고싶습니다.
그러면 지금 있는 코드에서 시트를 추가하는거를 없애고, 3번째 행부터 가져오는 코드로 변경해야할거같은데...
제가 만지작해서는 답이 안나오네요...
혹시 도움 주실 수 있으실까요..?

hih****
2020년 6월 1일 7:34 오전
별점주기 :
     

안녕하세요
영상 잘보고 많은 도움 됐습니다.

기본 코드로 만들면 새로운 "시트병합"시트가 생성 되는데 보통 시트병합시트를 한번 만들면 다시 "시트병합" 시트를 새로 만드는 것이 아니라 기존 "시트병합"시트에 덥어 씨우기를 하는 경우가 더 많은거 같습니다.
기존 데이터 일부 변경이 있을 경우 "시트병합"시트에 내용이 변경된 부분만 반영되게 하는 방법은 없을까요?

21
0
Would love your thoughts, please comment.x