[엑셀/VBA] 엑셀 시트별 모든 데이터 통합하기(합치기)

 

  이번 엑셀 VBA 포스팅은 엑셀 파일의 시트별 데이터를 통합시트에 모두 취합하는 코드를 작성하려고 한다. 코드를 작성하기 전에 작성 Concept은 아래와 같이 정리해본다.

 

 

Concept 1. 데이터가 있는 시트(DATA1,2,3,4...)와 "통합"시트가 한 파일에 존재한다. DATA 시트는 아래 이미지로는 5개지만 더 많아져도 상관없다.

Sheet 리스트

Concept 2. 시트별로 임의로 작성한 모델 및 가격 데이터가 있다. 시트명이 'DATA1'이라면 모델명은 1-알파벳으로 되어있다. 만약 시트명이 'DATA2'라면 2-알파벳인 데이터가 있다. 

 

DATA1 시트

 

Concept 3. VBA 코드 실행시 "통합"시트에 아래 이미지 처럼 모든 데이터를 취합한다.

"통합 시트 - VBA 실행 후 결과

먼저 전체 VBA 코드 및 결과부터 확인해보자.

 

 

1. 전체 코드 및 결과

 

<시트현황>

시트현황

 

<전체코드>

Option Explicit #명시적 선언
Sub 데이터통합()

    Dim i As Integer #시트번호를 변경시킬 변수 i
    Dim Sheetcount As Integer #Sheet 수를 저장할 변수
    Dim LastRow As Integer #마지막 행값을 저장할 변수
    
    Sheetcount = ActiveWorkbook.Sheets.Count #시트 숫자 세기
    
    Sheet1.UsedRange.Offset(1).Clear #통합시트의 데이터 초기화(모두 삭제)
    

    For i = 2 To Sheetcount #i=2부터인 이유는 Sheet1을 제외하기 위해서
    
        LastRow = Sheets(1).Cells(Rows.Count, 1).End(3)(2).Row #마지막 행값 읽어오기
        Sheets(i).UsedRange.Offset(1).Copy #각 DATA 시트의 데이터를 모두 복사
        Sheets(1).Cells(LastRow, 1).PasteSpecial xlPasteValues #통합 시트에 값 붙여넣기(통합시트가 1번일 경우)
        
    Next i

End Sub

 

<결과>

프로그램 결과

 

 

2. 코드 설명

 

 

1) Sheet 수 세기

 

Sheetcount = ActiveWorkbook.Sheets.Count

 

  활성화되어있는 엑셀파일의 시트 수를 세는 코드이다. 위 이미지를 확인해보면 "통합", DATA1~5 시트가 있으므로 Sheetcount는 6이다. 위 코드는 아래 코드로 대체하여도 같은 결과이다.

 

Sheetcount = ThisWorkbook.Sheets.Count

 

ThisWorkbook은 이전 포스팅에서도 설명한 적이 있는데 매크로(VBA)가 동작하는 엑셀파일을 의미한다. 즉, 현재 활성화되어있는 엑셀파일=매크로 동작 엑셀파일이므로 같은 결과이다.

 

 

 

2) 사용 영역 초기화 코드

 

Sheet1.UsedRange.Offset(1).Clear

 

위 코드를 순서대로 해석해보자.

 

코드 구분	설명
Sheet1	Sheet 1번, 즉 "통합" 시트를 이야기한다.
UsedRange	위 지정된 통합 시트에서 사용 중인 Range이다. 즉, 데이터가 입력되어있는 Cell 영역을 말한다.
Offset(1)	Range 개체에 사용할 수 있는 있다. Offset(a,b) 형태로 사용된다. 해석해보면 현재 지정 된 영역에서 행으로 a만큼, 열로 b만큼 Offset값을 주겠다는 것이다. 위 코드에서 Offset(1)이란, UsedRange가 1행부터 시작되는 영역이므로 2행부터라는 의미로 이해할 수 있다.
Clear	"꺠끗하게 지워라"라는 의미다. 값뿐만 아니라 셀서식도 지워진다. 만약 값만 지우고 싶다면, ClearContens를 사용한다. 값만 지우면 셀서식은 유지된다.

 

 

 

3) For 반복문

 

For i = 2 To Sheetcount #i=2부터인 이유는 Sheet1을 제외하기 위해서
    
        LastRow = Sheets(1).Cells(Rows.Count, 1).End(3)(2).Row #마지막 행값 읽어오기
        Sheets(i).UsedRange.Offset(1).Copy #각 DATA 시트의 데이터를 모두 복사
        Sheets(1).Cells(LastRow, 1).PasteSpecial xlPasteValues #통합 시트에 값 붙여넣기(통합시트가 1번일 경우)
        
Next i

 

-. LastRow = Sheets(1).Cells(Rows.Count, 1).End(3)(2).Row

:  마지막 행값을 구하는 LastRow의 경우 이전에 관련 포스팅을 작성한적이 있으므로 설명은 참고링크로 대체한다.

(참고링크 : 2021.06.08 - [코딩/엑셀,VBA] - [엑셀/VBA] 엑셀 시트 마지막 행값 구하기)

 

-. Sheets(i).UsedRange.Offset(1).copy

: For문의 i 정수값에 따라 Sheet별로 돌면서 데이터를 복사한다. UsedRange.Offset(1)의 경우 위에서 설명했던 부분으로 생략.

 

-. Sheets(1).Cells(LastRow, 1).PasteSpecial xlPasteValues

: "통합" 시트에 Cells(LastRow, 1) 에 복사한 값을 "값"만 붙여넣는다는 의미이다. 즉, For문을 진행하면서 마지막 행값 LastRow를 계속 업데이트하고 변화한 Cell 위치에 값을 붙여넣는 것이다. VBA에서 복사/붙여넣기의 경우 여러 옵션이 있는데 이 내용은 나중에 따로 포스팅해보도록 하겠다.

 

 

3. 응용 : 일부 영역 데이터만 가져오기

 

  만약, 위 코드처럼 시트별로 데이터를 전체 통합하는 것이 아니라 특정 위치 데이터만 가지고 오고싶다면 어떻게 할까? 위에 For문의 코드 중  Sheets(i).UsedRange.Offset(1).copy의 UsedRange.Offset(1)을 수정하면 된다.

 

3번째 데이터만 가져오기 위해 UsedRange.Offset(1)을 -> Range("A3:B3")로 수정하여 실행시켜보았다. 즉, 해당 줄의 코드는 Sheets(i).Range("A3:B3").Copy 이다.

 

 

<전체코드>

Option Explicit #명시적 선언
Sub 데이터취합2()

    Dim i As Integer
    Dim Sheetcount As Integer
    Dim LastRow As Integer
    
    Sheetcount = ActiveWorkbook.Sheets.Count #시트 숫자 세기
    Sheet1.UsedRange.Offset(1).Clear
    
    For i = 2 To Sheetcount #i=2부터인 이유는 Sheet1을 제외하기 위해서
    
        LastRow = Sheets(1).Cells(Rows.Count, 1).End(3)(2).Row
        Sheets(i).Range("A3:B3").Copy #Range 로 수정
        Sheets(1).Cells(LastRow, 1).PasteSpecial xlPasteValues
        
        
    Next i

End Sub

 

<결과>

Range("A3:B3") 데이터 가져오기