엑셀 각 시트의 자료 취합하기, 엑셀 각 시트의 자료 하나의 시트로 모으기

이번 블로그 내용은 처음부터 순차적으로 읽어보셔야

결론이 이해가 가실 수도 있습니다.

 

[ 각 시트의 구성 ]

1. [ A반, B반, C반 ] 이렇게 세개의 시트로 구성이 되어 있습니다.

 

2. 각 시트는 다음과 같이 구성되어 있습니다.

좌측에서 우측으로 [ A반, B반, C반 ]입니다.

 

[ 코 드 ]

Sub Combine_WorkSheets()
    Dim sRow As Long, sCol As Long, lRow As Long, lCol As Long
    Dim hdrs As Range ' Range로 수정
    Dim mtr As Worksheet
    Dim wb As Workbook
    Dim ws As Worksheet

    ' "Master" 워크시트가 이미 존재하는 경우 삭제
    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets("Master").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    ' "Master" 워크시트 생성
    Sheets.Add.Name = "Master"
    Set mtr = Worksheets("Master")
    Set wb = ActiveWorkbook

    ' 헤더 선택
    On Error Resume Next ' 에러 처리 추가
    Set hdrs = Application.InputBox("헤더를 선택하세요", Type:=8)
    On Error GoTo 0 ' 에러 처리 종료
    If hdrs Is Nothing Then Exit Sub ' 취소 버튼이 눌렸을 때 종료
    hdrs.Copy mtr.Range("A1")
    sRow = hdrs.Rows.Count + 1 ' 헤더의 행 수를 세는 방식으로 수정
    sCol = hdrs.Column

    For Each ws In wb.Worksheets
        If ws.Name <> "Master" Then
            lRow = ws.Cells(ws.Rows.Count, sCol).End(xlUp).Row
            lCol = ws.Cells(sRow, ws.Columns.Count).End(xlToLeft).Column
            ws.Range(ws.Cells(sRow, sCol), ws.Cells(lRow, lCol)).Copy mtr.Range("A" & mtr.Cells(mtr.Rows.Count, 1).End(xlUp).Row + 2)
        End If
    Next ws

    ' "Master" 워크시트 활성화 및 확대
    Worksheets("Master").Activate
    ActiveWindow.Zoom = 115
End Sub

 

[ 결 과 물 ]

위 코드를 실행하면 결과물이 다음과 같습니다.

 

[ 설  명 ]

중간중간 빈줄이 있습니다.

왜 이런 빈줄이 생기고 어떻게 해결하는 지 작성해 보겠습니다.

 

먼저 코드를 실행하면 아래 그림과 같이 범위를 선택하는 창이 나타납니다.

 

이렇게 창이 나타나면

각 시트에서 데이터가 들어 있지 않은 

공통된 제목셀의 범위를 선택해 줍니다.

[ A반 ]시트를 선택하고, 제목셀의 범위를 지정해 주었습니다.

그렇게 하고 확인을 누르면 앞서 말씀드렸던 결과물이 출력이 됩니다.

문제는 아래 부분의 코드에서 발생합니다.

ws.Range(ws.Cells(sRow, sCol), ws.Cells(lRow, lCol)).Copy mtr.Range("A" & mtr.Cells(mtr.Rows.Count, 1).End(xlUp).Row + 2)

위 코드의 빨간색 부분만 다시 적어 보겠습니다.

mtr.Range("A" & mtr.Cells(mtr.Rows.Count, 1).End(xlUp).Row + 2)

이 부분은 

먼저 [ A1048576 ]셀, 즉 A의 맨 아래 셀을 선택한 후

[ CTRL + ↑ ]를 누른다는 의미입니다.

 

그런데 코드를 실행했을 때 맨 처음 범위를 선택한 부분이

아래 그림과 같습니다.

 

여기서 문제가 발생합니다.

[ F8 ]을 눌러 코드를 한줄씩 실행해 보면

[ Master ] 시트가 맨 처음에는 아래 그림처럼 나타납니다.

mtr.Cells(mtr.Rows.Count, 1).End(xlUp).Row

이런식으로 셀을 선택하면 [ A4 ]셀이 선택이 되게 됩니다.

우리가 원하는 것은 [ A5 ]셀인데 말이죠

그래서 부득이하게 [ + 2 ]를 해주었습니다.

 mtr.Range("A" & mtr.Cells(mtr.Rows.Count, 1).End(xlUp).Row + 2)

그래야 [ A6 ]셀부터 데이터가 복사하여 채워지기 때문입니다.

그래서 그 다음 시트가 복사 될 때에는 중간중간 빈줄이 생기게 됩니다.

아래 그림처럼 말이죠...

 

반응형

[ 해결방법 ]

맨처음 엑셀 파일의 제목셀을 구성할 때 

셀 병합을 사용하는 것은 좋지 않습니다.

아래 그림과 같이 제목셀을 바꾸어 주면 좋습니다.

 

그렇게 한 후 코드를 아래와 같이 수정하면 됩니다.

ws.Range(ws.Cells(sRow, sCol), ws.Cells(lRow, lCol)).Copy mtr.Range("A" & mtr.Cells(mtr.Rows.Count, 1).End(xlUp).Row + 1)

수정된 전체 코드는 다음과 같습니다.

Sub Combine_WorkSheets()
    Dim sRow As Long, sCol As Long, lRow As Long, lCol As Long
    Dim hdrs As Range ' Range로 수정
    Dim mtr As Worksheet
    Dim wb As Workbook
    Dim ws As Worksheet

    ' "Master" 워크시트가 이미 존재하는 경우 삭제
    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets("Master").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    ' "Master" 워크시트 생성
    Sheets.Add.Name = "Master"
    Set mtr = Worksheets("Master")
    Set wb = ActiveWorkbook

    ' 헤더 선택
    On Error Resume Next ' 에러 처리 추가
    Set hdrs = Application.InputBox("헤더를 선택하세요", Type:=8)
    On Error GoTo 0 ' 에러 처리 종료
    If hdrs Is Nothing Then Exit Sub ' 취소 버튼이 눌렸을 때 종료
    hdrs.Copy mtr.Range("A1")
    sRow = hdrs.Rows.Count + 1 ' 헤더의 행 수를 세는 방식으로 수정
    sCol = hdrs.Column

    For Each ws In wb.Worksheets
        If ws.Name <> "Master" Then
            lRow = ws.Cells(ws.Rows.Count, sCol).End(xlUp).Row
            lCol = ws.Cells(sRow, ws.Columns.Count).End(xlToLeft).Column
            ws.Range(ws.Cells(sRow, sCol), ws.Cells(lRow, lCol)).Copy mtr.Range("A" & mtr.Cells(mtr.Rows.Count, 1).End(xlUp).Row + 1)
        End If
    Next ws

    ' "Master" 워크시트 활성화 및 확대
    Worksheets("Master").Activate
    ActiveWindow.Zoom = 115
End Sub

 

아직 내공이 부족하여 병합된 셀까지 처리하는 방법은 

잘 모르겠지만, 혹시 취합할 데이터를 만드실 때 

셀 병합을 사용하시는 것을 지양한다면

위 코드로 각 시트의 데이터를 하나로 쉽게 취합 하실 수 있을 것입니다.

 

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기