엑셀 시트 합치기 프로그램 만들기 :: 퀵 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 - '옵션'을 클릭합니다. 이후 유저폼을 불러오기 위하여 원하는 단축키로 설정 한 뒤, '확인'을 눌러 시트 합치기 프로그램 제작을 마무리 합니다. 이제 단축키를 입력하면 유저폼이 출력되며, 원하는 시트를 선택하여 병합할 수 있습니다.
유저폼을 불러오기 위하여 원하는 단축키를 설정 후 '확인'을 눌러 마무리합니다.
VBA 명령문을 어떻게 작성해야 할까요?
예를들어, 시트 A + 시트 B 를 병합한 뒤 'A열'에 중복된 데이터가 있는지 확인을 하고 싶으신건가요?:)
반환되는 동작에 따라 달라집니다. 예를 들어, 중복된 값이 있을 경우 중복된 값에 '배경색'을 입힌다거나 하는 방법이 있습니다.^_^
아래 명령문을 추가해보시겠어요?
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 Sub
첨부해주신 명령문을 Private Sub btnSubmit_Click() 끝에 추가하니, 잘 작동되는것을 확인 할 수 있었습니다.
추가로 반환되는 동작을 변경하고 싶어서 댓글 남깁니다.
반환되는 동작을 특정열에서 중복확인 후 중복되는 값이 있다면, 새로운 시트에 중복되는 값이 있는 행을
새로 만들어진 시트에 잘라 넣기를하는 동작으로 만들고 싶은데, 새로운 시트를 만드는 명령문까지는 만들었으나, 이후를 어떻게 해야할지 몰라 댓글 남겨봅니다.
감사합니다
Next
Dim fCondition As FormatCondition
Set rng = newWS.Range("D:D") '// 시트명과 범위를 변경하세요
With rng
.FormatConditions.AddUniqueValues
.FormatConditions(.FormatConditions.Count).SetFirstPriority
If .FormatConditions(1).DupeUnique = xlDuplicate Then Set newWS = WB.Worksheets.Add
newWS.Name = "시트병합내 중복값"
With newWS
.Cells(1, 1) = "1"
.Cells(1, 2) = "2"
.Cells(1, 3) = "3"
.Cells(1, 4) = "4"
end with
중복되는 값을 다른시트로 옮기는 경우는 조건부서식으로 처리할수가 없습니다.
배열을 사용하시거나, COUNTIF 함수로 다른 열에 에 해당셀의 출현빈도를 계산한 뒤, 출현빈도가 >1 인 경우 잘라서 붙여넣기 하시면 되는데요.
배열을 사용하는게 동작속도가 월등히 빠르나, 댓글로 적어드리기에는 많이 어려울듯 합니다.
향후 별도 명령문 snippet으로 작성해서 올려드릴께요 ^_^
COUNTIF 함수를 사용한 방법은 아래 링크를 참고해보시겠어요?
https://stackoverflow.com/questions/44176907/excel-vba-to-copy-duplicate-values-and-paste-it-another-sheets
For i = 2 To LastRow
If Sheets("시트명").Cells(i, "D").Value = "특정값일경우" Then '// 제거가능
Count = Application.WorksheetFunction.CountIf(Range("B1:B" & i), Sheets("Sample1").Cells(i, "B")) '// COUNTIF 함수로 개수를 셉니다.
If Count > 1 Then '// 개수가 1보다 크면
Sheets("Sample1").Cells(i, "E").EntireRow.Copy Destination:=Sheets("새로운시트명").Range("A" & Rows.Count).End(xlUp).Offset(1) '// 행 전체를 잘라서 새로운 시트에 붙여넣기 합니다.
End If
End If
Next i
다름이 아니라 혹시 완성파일도 받아볼 수 있을까 해서 문의 남깁니다 ㅎㅎ
완성파일과 예제랑 비교하면서 하나씩 따라해보면 좋을 것 같아서요!!
좋은 의견 감사드립니다. 이전에 미비했던 포스트를 하나둘씩 수정하고 있는데, 수정작업이 생각했던것보다 시간도 오래걸리고 손도 많이가네요..쿨럭 ㅠ-ㅠ;
완성파일은 아래 드롭박스 링크를 적어드릴테니 다운로드 받아서 확인해보시겠어요?^^* 링크 유효기간은 2020년 2월 1일까지입니다.
https://www.dropbox.com/s/pe8vd6hcgqty0c3/%ED%80%B5%20VBA%203%EA%B0%95%20-%20%EC%97%91%EC%85%80%20%EC%8B%9C%ED%8A%B8%20%ED%95%A9%EC%B9%98%EA%B8%B0%20%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%A8%20-%20%EC%98%88%EC%A0%9C%EC%99%84%EC%84%B1%ED%8C%8C%EC%9D%BC.xlsm?dl=0
감사합니다.
서식없이 값만 붙여넣기 하시려면,
Range(붙여넣기할셀).PasteSpecial xlPasteValues
로 입력해보시기 바랍니다. ^^
PasteSpecial 함수에 대한 자세한 설명은 아래 MS 홈페이지 링크(영문)를 참고해보시겠어요?
https://docs.microsoft.com/en-us/office/vba/api/excel.range.pastespecial
감사합니다.
파일 병합시 두가지 문제가 해결되지 않아서 요청드립니다.
1. 전 강의에서 알려주신것처럼 1번행에 특정 레이블을 넣고 싶은데, 어느곳에 어느 코드를 넣어야 되는지 문의 드립니다.
2. 복사해온 시트의 함수가 아닌 값만 가져오고자 하는데, 어떤부분을 수정해야 하는지 알려주시면 감사하겠습니다.
좋은 동영상 올려주셔서 다시한번 감사드립니다.
'// 파일병합
Set toWS = ActiveSheet
j = toWS.Cells(toWS.Rows.Count, 1).End(xlUp).Row
For i = 0 To Me.lstwb.ListCount - 1
Set WB = Application.Workbooks.Open(Me.lstwb.List(i))
For Each WS In WB.Worksheets
If WS.Name Like Me.txtfilter.Value & "시트목록" Then
With WS
endCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
endRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rng = .Range(.Cells(3, 15), .Cells(endRow, endCol))
rng.Copy toWS.Cells(j, 1)
j = j + rng.Rows.Count
End With
End If
Next
WB.Close
Next
첫번째 행에 특정 레이블을 넣는 코드는 해당 게시글의 완성 명령문에서 아래 부분에 해당합니다.
newWS.Name = "시트병합"
With newWS
.Cells(1, 1) = "매장"
.Cells(1, 2) = "날짜"
.Cells(1, 3) = "이름"
.Cells(1, 4) = "출근시간"
.Cells(1, 5) = "퇴근시간"
End With
확인 후 필요에따라 적절히 수정해보시겠어요?^^ 열을 추가할 경우, (1,6), (1,7).. 식으로 숫자를 증가해주시면 됩니다.
함수가 아닌 값만 붙여넣기 하시려면, 적어주신 명령문에서 아래 코드를
아래와 같이 바꿔보시겠어요?
toWs.Cells(j,1).PasteSpecial xlPasteValues
제시해드린 답변이 도움이 되셨길 바랍니다.^^
감사합니다.
저는 sheet1.range("a16:ad16").copy
sheet(newws).range("a1").pastespecial
이렇게 했는데 오류가 계속 나서요....
적어드린 코드 내용만으로는 정확한 답변을 드리기 어려워보입니다 .^^;
작성하신 전체 코드나 예제파일과 함께 커뮤니티 게시판에 글을 올려주시겠어요?
https://www.oppadu.com/question/
코딩 관련 답변은 어떤 오류가 발생하는지, 어떤 부분에서 오류가 발생하는지 보다 자세한 설명이 필요합니다.
시트 붙여넣기 후 중복된 매장이름을 셀 병합하고싶은데 VBA 명령문을 어떻게 작성해야 할까요 MERGE를 사용하고있는데 잘 되지 않아서요ㅜ.ㅜ
상황에 따라 다르겠으나, 아래와 같이 응용해보시기 바랍니다.
Dim Rng as Range
Set Rng = Range("A1")
If Rng.Value = Rng.Offset(0,1).Value Then Rng.Resize(1,2).Merge
답변이 도움이 되셨길 바랍니다.
아래 명령문을 응용하시면 원하는 개수만큼 시트를 복사할 수 있습니다.^^
제시해드린 답변이 도움이 되셨길 바랍니다.
감사합니다.
별도의 시트를 만들고, 그 시트내에 다른 시트들의 3번째 행부터 붙여넣기 하고싶습니다.
그러면 지금 있는 코드에서 시트를 추가하는거를 없애고, 3번째 행부터 가져오는 코드로 변경해야할거같은데...
제가 만지작해서는 답이 안나오네요...
혹시 도움 주실 수 있으실까요..?
아래와 같이 변경하시면 시트의 3번째 행부터 값을 복사/붙여넣기 합니다.^^
답변이 도움이 되셨길 바랍니다.
감사합니다.
영상 잘보고 많은 도움 됐습니다.
기본 코드로 만들면 새로운 "시트병합"시트가 생성 되는데 보통 시트병합시트를 한번 만들면 다시 "시트병합" 시트를 새로 만드는 것이 아니라 기존 "시트병합"시트에 덥어 씨우기를 하는 경우가 더 많은거 같습니다.
기존 데이터 일부 변경이 있을 경우 "시트병합"시트에 내용이 변경된 부분만 반영되게 하는 방법은 없을까요?