여러 시트 내용을 한 시트로 합치기

Step 1: 결과 미리 보기

잘 되지요? 이 작업을 하시려면 먼저, 7월과 8월 시트에 있는 각 상품명을 한데 합치는 작업을 먼저 해야 합니다. VB0028~30 강좌에서 설명드린 "중복 아이템 추려내기"를 사용하시면 쉽게 해결하실 수 있을 것입니다.

그럼 코드를 살펴보도록 하지요.

Step 2: 코드 살펴보기

Sub MakeTotal()
    Dim rngCell As Range
    Dim PName As Range      '//제품리스트群
    Dim Product7 As Range   '//7월 상품群
    Dim Product8 As Range   '//8월 상품群
    Dim rng7 As Range
    Dim rng8 As Range
    Dim i As Integer
    Dim lngSum As Long      '//누계
    Dim blnOK As Boolean    '//Flag
    Dim sht As Worksheet
    Dim Msg As String
    '//필요한 변수를 선언해 줍니다.

    Application.DisplayAlerts = False
    Application.Goto Sheets("제품리스트").Range("a1"), True

    Set PName = Sheets("제품리스트").Columns(1).SpecialCells(xlTextValues)
    Set Product8 = Sheets("8월").Range("A2", Sheets("8월").Range("A2").End(xlDown))
    Set Product7 = Sheets("7월").Range("A2", Sheets("7월").Range("A2").End(xlDown))
    '//각 Range 변수에 해당되는 영역을 할당합니다. 여기서 Pname은 각 월별 상품명이 모두 망라되어
    '//있는 범위임에 주의!

    For Each sht In Worksheets
        If sht.Name = "집계표" Then sht.Delete
    Next sht
    Worksheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "집계표"
    ActiveWindow.DisplayGridlines = False
    '//워크북 내에 "집계표"라는 시트가 있는 지 확인한 다음 있으면 해당 시트를 지우고 새로운 시트를
    '//한장 삽입합니다. 그 다음에 워크시트의 눈금선을 보이지 않도록 설정합니다.

    With Range("a1")
        .Value = "상품명"
        .Offset(0, 1) = "8월 수량"
        .Offset(0, 2) = "7월 수량"
        .Offset(0, 3) = "수량계"
    End With
    '//새로 삽입한 시트의 타이틀 부분을 만들고…

    For Each rngCell In PName
        lngSum = 0
        blnOK = False
        For Each rng8 In Product8
            If rngCell = rng8 Then
                blnOK = True
                i = i + 1
                With ActiveCell
                    .Offset(i, 0) = rngCell
                    .Offset(i, 1) = rng8.Offset(0, 1)
                    .Offset(i, 3) = rng8.Offset(0, 1)
                End With
                Exit For
            End If
            '//"제품리스트" 부분에 있는 각 상품명을 가지고 먼저, "8월" 시트의 해당값과 일일이 비교를 합
            '//니다. 그래서 같은 값이 발견되면 상품명, 8월 수량, 수량계 부분에 값을 집어 넣습니다. 여기
            '//blnOK라는 불리언(Boolean) 변수의 용도를 잘 파악하셔야 합니다. 8월에도 실적이 있고 7월
            '//에도 실적이 있는 상품일 경우에는 8월 실적이 있으면 있다고 어떤 표시를 해 두어야 헷갈리
            '//지 않겠지요?

            '//예를 들어, 책을 읽고 있는데 누가 불러서 밖에 나갈 경우, 책에 어떤 표시를 해 두든지 아니면
            '//메모지에 몇 쪽 몇째줄까지 읽었는지 적어두어야 돌아왔을 때 헷갈리지 않고 바로 찾아가겠
            '//지요? 바로 이 책갈피 또는 메모지 역할을 하는 것이 바로 이 blnOK 변수입니다.

        Next rng8

        For Each rng7 In Product7
            If rngCell = rng7 Then
                If blnOK Then
                    With ActiveCell
                        .Offset(i, 2) = rng7.Offset(0, 1)
                        .Offset(i, 3) = .Offset(i, 3) + rng7.Offset(0, 1)
                    End With
                '//이번에는 7월 시트 중에서 같은 상품명이 있나없나를 비교합니다. 그런데 7월 시트에 있는
                '//상품명은 두 종류로 분류가 되어질 것입니다. 즉 8월 시트에 있으면서 7월에도 있는 것이
                '//있을 것이고, 7월에만 있는 것도 있겠지요?
                '//이 윗부분에서는 전자일 경우 처리하는 과정입니다. If blnOK Then… 과 같이 책갈피가 여
                '//기서 사용되었습니다.

                Else    '//7월에만 자료가 있을 경우
                    i = i + 1
                    With ActiveCell
                        .Offset(i, 0) = rngCell
                        .Offset(i, 2) = rng7.Offset(0, 1)
                        .Offset(i, 3) = rng7.Offset(0, 1)
                    End With
                End If
                blnOK = False
                    '//여기서 blnOK를 다시 False로 해 주어야 새로운 상품명을 검색할 때 제대로 검색을 할
                    '//수 있겠지요? 만약 다시 False로 되돌리지 않으면 새 상품명이 나올 때마다 8월에 모두
                    '//실적이 있는 것으로 컴퓨터가 인식을 하게 될 것입니다. 마치 책의 모든 페이지에다가
                    '//책갈피를 해 둔 것과 같다고 할 수 있을 것입니다.

                Exit For
            End If
        Next rng7
    Next rngCell

    Range("a2").Select
    ActiveWindow.FreezePanes = True
    If i > 15 Then ActiveWindow.SmallScroll down:=1
    '//만약 행 수가 15행을 넘어가다면 틀고정을 하기 위한 과정입니다.

    Range("A1").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Columns("B:D").NumberFormat = "#,##0"
    Range("A1").CurrentRegion.AutoFormat Format:=xlRangeAutoFormatList2
    '//이 부분은 자료 통합을 다 한 다음에 Sorting을 하고 모양을 보기좋게 꾸미는 과정입니다. 이것을
    '//다 외워서 코딩을 할 필요는 물론 없습니다. 매크로 기록을 했다가 붙여넣기를 하면 되지요.

    With Cells.Font
        .Name = "바탕"
        .Size = 12
    End With
    With Range("a2").CurrentRegion
        .Borders(xlLeft).Weight = xlHairline
        .Borders(xlLeft).ColorIndex = xlHairline
        .Borders(xlRight).Weight = xlHairline
        .Borders(xlRight).ColorIndex = xlHairline
        '//마찬가지로 통합된 자료의 서식을 꾸미기 위한 부분으로 괘선을 그리는 과정입니다.

    End With
    Columns.AutoFit

    Rows("1:3").Insert shift:=xlDown
    Range("a1") = "제품별/월별 판매수량 실적"
    Range("a1").Select
    With Selection.Font
        .Name = "HY견고딕"
        .Size = 20
        .ColorIndex = 5
    End With

    Range("D3").Select
    With Selection
        .Value = "단 위: 개"
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
    End With
    With Selection.Font
        .Name = "바탕"
        .Size = 10
    End With

    '//여기서부터는 현재 셀에 버튼을 하나 만든 다음, 버튼의 서식을 설정하고 프로시저를 연결하여
    '//원래 시트로 되돌아 가도록 만드는 과정입니다.
    Range("a3").Select
    With ActiveCell
        ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height).Select
        Selection.OnAction = "GoBack"
        '//다른 별 것은 없을 것이고… 버튼에 코드를 메달 때, OnAction 속성을 사용한다는 것만 기억하고
        '//넘어가면 될 것 같습니다.

        With Selection.Characters
            .Text = "원래 위치로 돌아가기"
            With .Font
                .Name = "굴림"
                .Size = 10
                .ColorIndex = 3
            End With
        End With
    End With
    Range(Selection.TopLeftCell.Address).Select
    '//TopLeftCell이라는 새로운 속성이 하나 나왔군요. 지금 상태에서 Selection이면 무엇이지요? 그렇
    '//습니다! 버튼이 Select 된 상태입니다. 즉, 현재 버튼이 놓여져 있는 "위쪽 끝셀"이니까 A3셀이지요.

    Msg = "자료 통합을 완료하였습니다" & vbCr
    Msg = Msg & "이런 방법으로 몇 개의 시트든지 통합할 수 있겠지요?" & vbCr & vbCr
    Msg = Msg & "버튼을 누르세요..."
    Application.DisplayAlerts = True
    MsgBox Msg, , "자료 통합하기//Exceller"
End Sub

Sub GoBack()
    Sheets("Preface").Select
End Sub

코드 구조 정리

코드가 좀 길어서 복잡해 보이는데 몇 도막으로 나누어서 보면 크게 어려운 부분은 없을 것입니다. 복잡하게 보일 따름이지 어려운 코드는 아닙니다. 주말맞이 기념(?)으로 좀 길게 만들었습니다. ^^

이 코드를 몇 도막으로 나누어 살펴보면…

1. (1) 각 월별로 상품명을 하나로, 중복없이 합치는 작업(이 부분은 코드안에 포함시키지는 않았습니다)
2. (2) (1)에서 만들어진 리스트를 가지고 8월 시트와 비교
3. (3) (1)의 결과를 7월 시트와 다시 비교하는 과정
4. (4) 시트를 하나로 합친 다음 서식 꾸미기
5. (5) 버튼을 하나 만든 다음, 원래 시트로 돌아가는 프로시저와 연결하기

이렇게 다섯 단계로 구분이 되겠군요. 잘 소화하셔서 완전히 내 것으로 붙이시기 바랍니다.

마무리

오늘은 여기까지…

정리 — 시트 통합 핵심

자주 묻는 질문 (FAQ)

마치며

코드가 길어 보여도 몇 도막으로 나누어 보면 어려운 부분은 없습니다.