여러 시트에 데이터를 하나의 시트로 합치는 간단한 VBA

이번 작업은 여러 시트의 내용을 하나의 시트로 합치는 작업입니다.

아주 많은 분들이 자주 물어보는 문제 중 하나입니다.

수식, 함수로는 해결되지 않는 문제인데, VBA로 간단하게 해결할 수 있습니다.

 

실습 파일 다운로드

여러시트합치기_실습파일.xlsx

0.01MB

 

완성 파일 다운로드

여러시트합치기_완성파일.xlsm

0.02MB

 

현재 엑셀 파일에 "A대리점", "B대리점", "C대리점" 세개의 시트가 있습니다.

 

 

우리가 할 일은 다음과 같습니다.

 

1. 새로운 시트를 만듭니다.

2. A대리점 시트의 내용을 복사해서 새 시트에 붙여넣습니다.

3. B대리점 시트의 내용을 복사해서 새 시트에 삽입된 A대리점 시트 내용 밑에 붙여넣습니다.

4. C대리점 시트의 내용을 복사해서 새 시트에 삽입된 B대리점 시트 내용 밑에 붙여넣습니다.

 

새 시트 만들기

새 시트를 삽입하려면 VBA로 어떻게 해야할지 전혀 감도 잡히지 않죠?

한 번도 해 본적이 없기 때문이죠?

 

그렇다고 포기할 필요없습니다.

인터넷에서 검색해 보면 왠만한 내용은 다 나옵니다.

특히 시트를 삽입하는 것과 같은 아주 많은 사람이 필요할 것 같은 이런 문제엔 소개글도 많죠.

 

그러다 문득 드는 생각이 '매크로는 엑셀 작업을 VBA 코드로 기록하는 일이다'라는 겁니다.

매크로 기록을 시작해서 새 시트를 삽입한 뒤 기록된 매크로 내용을 보면 VBA 코드를 확인해 볼 수 있겠죠?

 

[개발 도구]-[매크로 기록]을 선택합니다.

[매크로 이름]을 따로 바꿀 필요없이 바로 [확인] 단추를 눌러 기록을 시작합니다.

[홈]-[삽입]-[시트 삽입]을 누릅니다.

새 시트가 삽입된 걸 확인하고 [개발 도구]-[기록 중지]를 누릅니다.

매크로 기록 중엔 꼭 필요한 작업만 하고 바로 끝을 냅니다.

 

[개발 도구]-[매크로]를 누릅니다.

매크로를 선택하고 [편집] 단추를 누릅니다.

VBE 창이 실행되고 기록된 매크로 코드 내용이 보입니다.

예상하지 못할만큼 간단한 코드죠?

 

제대로 쓰려면 몇 가지 더 알아야 합니다.

시트를 삽입할 때 '어느 시트 앞이나 뒤에 삽입할 것인지'나 '삽입될 시트의 이름을 뭘로 할지', '만약 같은 이름의 시트가 이미 있다면 어떻게 할건지'와 같은 것들이죠.

이번 강좌에서는 고려할 필요 없으니 이런 부분 해결은 다음 기회로 미루죠.

 

필요한 VBA 코드를 알아냈으니 지금 만들어진 매크로는 필요없지만, 어차피 작업을 하려면 새 프로시저를 만들어야 하는데, 이 매크로 이름을 바꿔 그냥 사용하죠.

 

매크로 이름을 '여러시트합치기'로 바꿉니다.

새로 삽입한 시트에 이름을 지정해 다른 시트와 구분하는 것이 다음 작업을 쉽게 하는 방법입니다.

 

새 시트의 이름을 "합치기"로 지정합니다.

Sheets(1).Name = "합치기"

이때 주의할 점이 있는데, 시트를 삽입할 때 어떤 시트에서 시작했느냐에 따라 시트가 삽입되는 위치가 달라집니다.

 

매크로를 만들 때 첫번째 시트에 커서를 두고 만들어서 새로 삽입된 시트가 현재 첫번째 시트입니다.

 

Sheets 컬렉션에서 첫번째 시트는 Sheets(1), 두번째 시트는 Sheets(2)라는 형식으로 지정됩니다.

또는 시트의 이름 Sheets("A대리점")으로 지정할 수도 있습니다.

 

Name은 속성(Attribute)인데 시트의 이름을 나타내거나 설정할 때 사용합니다.

 

시트를 삽입할 때 어떤 시트 앞이나 뒤에 나타낼건지 결정할 수 있는 인수가 있습니다.

이 인수를 이용하면 원하는 위치를 지정해 어떤 시트에서 시트를 삽입하든 문제없이 동작하는 매크로를 만들 수 있습니다.

그렇지만 그렇게 만들면 너무 복잡해 집니다.

만들고 있는 이 매크로는 다른 사람이 쓸 매크로가 아니라 우리가 사용할 예정이니 첫번째 시트에 커서를 두고 매크로를 실행하는 것으로 정하고 그렇게 사용하면 됩니다.

그러면 코드를 작성하는 노력과 시간이 많이 절약되죠.

 

눈치채셨는지 모르겠지만, 이 VBA 강좌는 개발자를 위한게 아니라 사용자를 위해 하고 있는겁니다. ^^

 

A대리점 시트의 내용을 복사해서 합치기 시트에 붙여넣기

이제 데이터를 합쳐 보관할 시트가 만들어 졌으니 각 대리점 시트의 내용을 하나씩 복사해서 가져와 붙여넣으면 됩니다.

 

시트를 옮겨 입력된 데이터가 아무리 많아도 한꺼번에 선택해 복사하고 합치기 시트에 가져와 붙여넣는 VBA 코드가 머리에 떠오르시나요?

 

머리에 VBA 코드가 떠오르지 않아도 됩니다.

지금은 매크로를 만들면 VBA 코드가 만들어 진다는 것만 기억하면 됩니다.

 

VBE는 뒤로 미뤄두고 다시 엑셀로 갑니다.

<Alt+F11>을 누르면 됩니다.

 

[개발 도구]-[매크로 기록]을 선택합니다.

매크로 이름은 그대로 사용합니다.

1. A대리점 시트를 선택합니다.

2. [A3] 셀을 선택합니다.

3. <Ctrl + 화살표 오른쪽>을 누릅니다.

4. <Ctrl + 화살표 아래쪽>을 누릅니다.

5. 선택된 범위에서 마우스 오른쪽을 눌러 복사하기를 선택합니다.

6. 새 시트를 삽입합니다.

7. [A1] 셀을 선택합니다.

8. 마우스 오른쪽을 눌러 붙여넣기를 선택합니다.

 

만들어진 매크로의 내용을 확인합니다.

그런데 이번엔 [개발 도구]-[매크로]-[편집]을 눌러선 확인할 수 없습니다.

이미 VBE가 실행되어 있기 때문입니다.

 

[개발 도구]-[Visual Basic]을 눌러 VBE를 활성화합니다.

 

매크로2 코드를 보면 매크로 기록할 때 했던 동작 하나하나가 코드로 기록되어 있는걸 볼 수 있습니다.

이 코드를 그대로 여러시트합치기 프로시저에 복사해서 사용해도 됩니다.

한가지 고쳐야 할 부분은 Sheets("Sheet1").Select 코드에서 시트 이름을 바꿔야 합니다.

Sheets("합치기").Select로 바꾸면 되죠.

B대리점 시트의 내용을 복사해서 삽입된 A대리점 시트 내용 밑에 붙여넣기

B대리점 시트의 내용을 복사해 가져오는 부분은 A대리점 시트의 내용을 가져오는 VBA 코드와 거의 같습니다.

차이점은 합치기 시트에 붙여넣기 할 때 이미 앞에 삽입된 A대리점 시트의 내용 아래에 연속해서 삽입되어야 한다는 겁니다.

각 대리점 시트에 입력된 내용들이 지금 입력된 개수와 다음번에 실행될 개수가 똑같지 않을 것이기 때문에 범위를 선택할 때 <Ctrl + 화살표>를 이용한 방법으로 지정을 한 겁니다.

 

이번에도 매크로 기록을 해 코드를 만들어 보는데, 상대참조로 기록을 하죠.

 

<Alt+F11>을 눌러 엑셀로 돌아갑니다.

 

새 시트 [A1] 셀을 클릭합니다.

[개발 도구]-[상대 참조로 기록]을 클릭합니다.

[매크로 기록]을 누릅니다.

1. <Ctrl+화살표 아래쪽>을 누릅니다.

2. 화살표 아래쪽을 한 번 누릅니다.

 

<Alt+F11>을 눌러 VBE로 돌아갑니다.

매크로3 코드 중 첫번째는 현재 선택된 [A1] 셀을 의미합니다.

Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

지금은 [A1] 셀이 선택되어 있어 상관 없지만 다음에 실행될 땐 다른 위치에 선택되어 있을 수 있어 직접 [A1] 셀이 선택되도록 코드를 수정합니다.

Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

그리고 A대리점 데이터를 가져오도록 만들어진 코드를 수정해 B대리점 데이터를 가져오는 코드로 마무리 합니다.

A대리점은 데이터를 복사할 때 제목행부터 복사하기 위해 [A3] 셀에서 시작했지만 B대리점은 [A4] 셀부터 시작합니다.

C대리점 시트의 내용을 복사해서 붙여넣기

C대리점 시트 데이터를 복사하는 것은 B대리점 시트 내용 복사 코드를 조금 수정해서 완성합니다.

Sub 여러시트합치기()
'
' 매크로1 매크로
'

'
    Sheets.Add
    Sheets(1).Name = "합치기"
    
    Sheets("A대리점").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("합치기").Select
    ActiveSheet.Paste
    
    Sheets("B대리점").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("합치기").Select
    
    Range("A1").End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    
    ActiveSheet.Paste
    
    Sheets("C대리점").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("합치기").Select
    
    Range("A1").End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    
    ActiveSheet.Paste
End Sub

 

마무리

제대로 실행되는지 확인하기 위해 <Alt+F11>을 눌러 엑셀로 돌아갑니다.

 

삽입된 시트를 삭제합니다.

 

어치피 VBA로 새로 만들 예정이므로 경고 메시지는 무시하고 [삭제] 단추를 누릅니다.

 

[개발 도구]-[매크로]를 누릅니다.

매크로 목록에서 '여러시트합치기'를 선택하고 [실행]을 누릅니다.

[실행] 단추를 누르기 전에 무사히 동작하길 기원하는 기도를 살짝 올려도 됩니다.

만든 코드가 한 번에 잘 실행되는 경우는 어차피 잘 없긴 합니다.

약간 화면이 멈춘듯 있다 짠하고 합치기 시트가 나타납니다.

혹시 오류가 생긴다면 VBE에서 코드를 한 줄 한 줄 다시 보며 수정하거나 싹 지워버리고 처음부터 다시 따라해 보세요.

 

 

다시 실행해 제대로 되는지 확인해 보려면 꼭 합치기 시트를 지우고 다시 해야 합니다.

만들어진 코드에는 똑같은 이름의 시트가 있는지 검사하고 처리하는 기능은 없기 때문입니다.

 

중간중간 VBA 코드를 확인하기 위해 만든 임시 매크로를 삭제합니다.

[파일]-[다른 이름으로 저장]을 선택합니다.

 

파일 형식을 [Excel 매크로 사용 통합 문서(*.xlsm)]으로 선택하고 [저장] 단추를 누릅니다. 

 

좀 더 여러 곳에서 쓸 수 있게 만들기

앞에서 만든 코드로 충분합니다.

더 이상 손대지 않아도 충분합니다.

 

그런데 VBA라기 보다는 매크로 코드를 대부분 썼고, 시트가 여러 개 있다면 불편하죠.

 

완성된 코드를 참고해서 시트가 여러 개인 경우에도 한 번에 실행되도록 만들어 보겠습니다.

 

새 프로시저를 삽입합니다.

프로시저 이름은 여러시트합치기2로 합니다.

매크로 기록으로 만든 프로시저와 프로시저 추가로 만든 코드에 차이점이 있는데, 앞의 Public은 프로시저의 사용범위를 나타내는 예약어이지만 없어도 실행하는데 차이가 됩니다.

Public Sub 여러시트합치기2()

End Sub

새 시트를 추가하면서 새 시트가 나타날 위치를 첫번째 시트 앞으로 지정합니다.

이제 어느 시트에서 프로시저를 실행하던 통합문서의 첫번째 시트가 합치기 시트가 됩니다.

Sheets.Add before:=Sheets(1)
Sheets(1).Name = "합치기"

여기까지만 입력한 상태에서 코드를 실행해 새 시트가 삽입되는지, 삽입된 시트의 이름이 합치기로 변경되는지를 확인해 볼 수 있습니다.

커서를 여러시트합치기2 프로시저에 두고 화면 위쪽 표준 도구모음의 실행 단추를 누르거나 단축키 <F5>를 눌러 실행합니다.

아무런 오류메시지 없이 코드가 실행되어 합치기 시트가 나타났다면 이 부분은 제대로 만들어진 코드임을 검증받은 겁니다.

 

아래쪽에 계속 코드를 입력해 중간중간 테스트하기 위해 코드를 실행하면 다시 시트를 삽입하는 부분까지 실행되어 오류가 날 수 있으니 이 코드는 일단 주석처리해 둡니다.

 

각 코드 줄 앞에 작은 따옴표( ' )를 입력하면 주석이 됩니다.

두번째 시트인 A대리점 시트에서 제목행을 복사해 합치기 시트에 붙여넣습니다.

제목행은 한 번만 붙여넣으면 되고 데이터를 복사하는건 여러 시트에서 복사해야 되므로 반복문을 적기 전에 먼저 적습니다.

 

두번째 시트를 활성화 합니다.

Sheets(2).Activate

[A3] 셀에 커서를 두고 <Ctrl+화살표 오른쪽>키를 눌러 범위 선택된 곳까지 복사합니다.

Range("A3", Range("A3").End(xlToRight)).Copy

첫번째 시트를 활성화 합니다.

Sheets(1).Activate

[A1] 셀을 선택합니다.

Range("A1").Select

첫번째 시트에 붙여넣기 합니다.

Sheets(1).Paste

코드를 입력할 때 마침표를 찍으면 목록창이 나타나는 경우도 있고, 나타나지 않는 경우도 있습니다.

목록창이 나타나지 않는다고 꼭 개체를 잘못 적은 건 아닙니다.

 

코드를 실행해 보면 A대리점의 [A3:C3] 범위가 복사되어 합치기 시트 [A1:C1] 범위에 붙여넣어 집니다.

데이터는 [A2] 셀부터 나타나도록 미리 [A2] 셀을 선택합니다.

Range("A2").Select

여기까지 전체 코드입니다.

Public Sub 여러시트합치기2()
    'Sheets.Add before:=Sheets(1)
    'Sheets(1).Name = "합치기"
    
    Sheets(2).Activate
    Range("A3", Range("A3").End(xlToRight)).Copy
    Sheets(1).Activate
    Range("A1").Select
    Sheets(1).Paste
    
    Range("A2").Select
    
End Sub

이제부터 해야하는 작업은 두번째 시트부터 마지막 시트까지 차례대로 데이터 부분을 복사해서 첫번째 시트에 붙여넣는 겁니다.

이때 앞에 붙여넣은 데이터 아래에 다음번 시트의 데이터를 붙여넣기 해야 합니다.

 

변수 i에 숫자 2를 넣습니다.

변수 i는 두번째 시트부터 마지막 시트까지 작업할 시트 번호를 담을 목적으로 만들었습니다.

하필 a나 b가 아니고 소문자 i로 쓰는 이유는 관례 같은 것이라고 해 두죠.

 

Do ~ Loop 문은 반복 실행되는 내용을 담는 명령문입니다.

i = 2

Do

Loop

Do부터 Loop 사이의 코드가 주어진 조건에 만족하거나 불만족할 때까지 반복 실행됩니다.

그래서 꼭 조건을 적어야 합니다.

 

여기서는 최소 1개 이상의 시트에서 데이터를 복사할 것이기 때문에 Loop문 뒤에 조건 While를 적겠습니다.

 

Do~Loop 범위 안에 포함되는 코드는 들여쓰기 해서 포함 관계를 시각적으로 확인할 수 있도록 코딩합니다.

 

변수 i는 처음엔 2이고, Do~Loop를 한 번 돌면 3, 두 번 돌면 4, 이런 식으로 1씩 증가하며 커지게 됩니다.

Loop 뒤의 While에 조건을 지정했는데, 변수 i의 값이 전체 시트 수보다 적거나 같을 때까지 동작합니다.

i = 2

Do

    i = i + 1
Loop While i <= Sheets.Count

정상적으로 동작하겠지만, 혹시 반복문 안의 코드나 조건의 문제로 무한반복되는 경우 강제로 멈출 수 있도록 DoEvents 메서드를 반복문 안에 넣어 둡니다.

코드를 완성해 정상적으로 처리되면 DoEvents는 지워도 됩니다.

보험을 하나 들어 두는 겁니다.

i = 2

Do

    i = i + 1

    DoEvents
Loop While i <= Sheets.Count

다음번째 시트를 선택합니다.

[A4] 셀에서 <Ctrl + 화살표 오른쪽>키를 누르고 이어서 <화살표 아래쪽>키를 눌러 선택한 범위를 복사합니다.

End(xlToRight) 코드가 <Ctrl + 화살표 오른쪽>을 의미하고 End(xlDown) 코드가 <Ctrl + 화살표 아래쪽>을 의미합니다.

아래쪽에서 첫번째 시트를 활성화 하고, 붙여넣기 합니다.

 

앞에서 Range("A2").Select 코드를 입력해 둬서 그냥 첫번째 시트에 붙여넣기 하면 [A2] 셀부터 붙여넣기 됩니다.

 

i = 2

Do
    Sheets(i).Activate
    Range("A4", Range("A4").End(xlToRight).End(xlDown)).Copy
    Sheets(1).Activate
    Sheets(1).Paste


    i = i + 1

    DoEvents
Loop While i <= Sheets.Count

붙여넣기 한 다음 커서 위치를 붙여넣기된 내용 다음 위치로 옮겨둬야 다음번 시트 데이터를 복사할 때 위치가 맞게 됩니다.

 

첫번째 시트 [A1] 셀에서 <Ctrl + 아래쪽 화살표>를 눌러 행 방향으로 제일 아래 셀로 이동합니다.

그 위치에서 행 방향으로 한 칸 더 아래 셀을 선택합니다.

i = 2

Do
    Sheets(i).Activate
    Range("A4", Range("A4").End(xlToRight).End(xlDown)).Copy
    Sheets(1).Activate
    Sheets(1).Paste

    Range("A1").End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select

    i = i + 1

    DoEvents
Loop While i <= Sheets.Count

여러시트합치기2 프로시저가 완성되었습니다.

 

중간에 코드가 제대로 실행되는지 테스트를 해 보고 싶었지만 반복 실행되는 코드이고, 코드가 연속해서 입력되어야 테스트가 가능한 부분이어서 하지 않았습니다.

Public Sub 여러시트합치기2()
    'Sheets.Add before:=Sheets(1)
    'Sheets(1).Name = "합치기"

    Sheets(2).Activate
    Range("A3", Range("A3").End(xlToRight)).Copy
    Sheets(1).Activate
    Range("A1").Select
    Sheets(1).Paste

    Range("A2").Select

    i = 2

    Do
        Sheets(i).Activate
        Range("A4", Range("A4").End(xlToRight).End(xlDown)).Copy
        Sheets(1).Activate
        Sheets(1).Paste

        Range("A1").End(xlDown).Select
        ActiveCell.Offset(1, 0).Range("A1").Select

        i = i + 1

        DoEvents
    Loop While i <= Sheets.Count

End Sub

표준 도구모음의 [실행] 단추나 단축키 <F5>를 눌러 실행해 봅니다.

 

합치기 시트에 데이터가 다 복사된 것이 보이죠?

 

대리점 시트의 값과 합치기 시트에 나타나 있는 값을 비교해 보아야 합니다.

C대리점 시트의 마지막 값인 비디오, 24, 13400이 다 나타나 있군요.

 

그럼 마지막으로 전체 테스트를 해 봅니다.

코드 중 시트를 만드는 코드는 주석으로 만들어 두었는데, 주석을 해제합니다.

DoEvents 코드는 삭제합니다.

Public Sub 여러시트합치기2()
    Sheets.Add before:=Sheets(1)
    Sheets(1).Name = "합치기"

    Sheets(2).Activate
    Range("A3", Range("A3").End(xlToRight)).Copy
    Sheets(1).Activate
    Range("A1").Select
    Sheets(1).Paste

    Range("A2").Select

    i = 2

    Do
        Sheets(i).Activate
        Range("A4", Range("A4").End(xlToRight).End(xlDown)).Copy
        Sheets(1).Activate
        Sheets(1).Paste

        Range("A1").End(xlDown).Select
        ActiveCell.Offset(1, 0).Range("A1").Select

        i = i + 1

    Loop While i <= Sheets.Count

End Sub

 

합치기 시트도 삭제합니다.

엑셀 창에서 [개발 도구]-[매크로]를 선택합니다.

매크로 선택창에서 여러시트합치기2를 선택하고 [실행] 단추를 누릅니다.

오류 없이 제대로 실행되었습니다.

필요하신 분들께 도움이 되시길 바라며 강좌는 마치겠습니다
수고하셨습니다.