엑셀 시트 합치기 프로그램 만들기 :: 퀵 VBA 2강
엑셀 시트 합치기 프로그램 목차 바로가기
강의 요약
엑셀은 아주 훌륭한 프로그램이고 현재 공유기능을 지원하고 있지만, 그럼에도 불구하고, 아직까지는 많은 사람이 동시에 같은 자료를 관리하기에 다소 부족한 부분이 있습니다. 그러다보니, 실제 현업에서는 동일한 형태의 시트를 각 부서 또는 각 매장별로 나눠서 관리하는 경우가 많은데요. 이번 강의에서는 이렇게 여러개로 나뉘어진 시트 중에 원하는 시트만 선택해서 하나로 합치는 시트 합치기 프로그램 제작 방법을 단계별로 알아보겠습니다.
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀VBA] 엑셀 시트합치기 모듈 (시트병합 프로그램)예제파일[엑셀VBA] 엑셀 시트합치기 모듈 (시트병합 프로그램)완성파일
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 함수에 대한 자세한 설명은 아래 링크를 참고하세요. '// https://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 '// 시트의 마지막 행/열 받아오기에 대한 자세한 설명은 아래 링크를 참고하세요. '// https://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 편집기를 실행합니다. 이후 '삽입' - '사용자 정의 폼'을 추가한 뒤, 아래와 같이 각 컨트롤을 추가합니다.
만약 엑셀 화면에 개발도구가 보이지 않을경우, 아래 개발도구 활성화하기 관련 포스트를 확인하세요.
원하는 시트를 선택후 병합하기 위해 사용자 정의폼을 생성합니다. 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 시트 병합을 위하여 새로 생성되는 워크시트입니다. i For 문 / 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가지 오류를 처리구문을 작성합니다.
- 리스트박스에서 아무런 항목도 선택되지 않은 채 사용자가 버튼을 클릭했을 경우
- 기존 ‘시트병합’ 시트가 존재할 경우
'// isListBoxSelected 함수에 대한 자세한 설명은 아래 링크를 참고하세요. '// https://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 '// 시트의 마지막 행/열 받아오기에 대한 자세한 설명은 아래 링크를 참고하세요. '// https://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 - '옵션'을 클릭합니다. 이후 유저폼을 불러오기 위하여 원하는 단축키로 설정 한 뒤, '확인'을 눌러 시트 합치기 프로그램 제작을 마무리 합니다. 이제 단축키를 입력하면 유저폼이 출력되며, 원하는 시트를 선택하여 병합할 수 있습니다.
유저폼을 불러오기 위하여 원하는 단축키를 설정 후 '확인'을 눌러 마무리합니다.
Sub Test() Dim rng As Range Dim fCondition As FormatCondition Set rng = Sheet1.Range("A:A") '// 시트명과 범위를 변경하세요 With rng .FormatConditions.AddUniqueValues .FormatConditions(.FormatConditions.Count).SetFirstPriority With .FormatConditions(1) .DupeUnique = xlDuplicate .Font.Color = RGB(255, 0, 0) '// 중복값이 있을경우 적용할 서식을 입력합니다. End With End With End SubFor i = 1 to 원하는개수 Worksheets("시트명").Copy ThisWorkbook.Worksheets(1) Next제시해드린 답변이 도움이 되셨길 바랍니다. 감사합니다.