엑셀_VBA_여러 시트의 데이터 한 시트에 모으기

[엑셀TIP] 여러 시트의 데이터 한 시트에 모으기

1. 엑셀 파일 열고

2. ALT+F11로 편집기 열고

  프로젝트명 선택 > 삽입 > 모듈

 

VBAproject(파일명) 선택하고 오른쪽 클릭해서 삽입 > 모듈 그리고 소스 붙이기[소스]--------------------------------------Sub 시트합치기()

    Dim i As Integer
    On Error Resume Next
    
    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "합쳐진 시트"
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    
    For i = 2 To Sheets.Count
    Sheets(i).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    
    Next

End Sub

---------------------------------------------------

3. 엑셀파일 xlsm으로 저장하고 

     xlsm 파일 열고

4. ALT+F8(매크로 실행) 

 

출처: https://m.blog.naver.com/geni4160/222205378206

오늘은 여러 시트에 나누어져 있는 데이터를

VBA를 사용해서 쉽게

하나의 시트로 모으는 방법을 알아볼게요.

즉, 우리가 하고 싶은 것은

여러개의 시트로 분산된 데이터들을

하나의 시트로 취합하는 거죠.

​

지금 바로 시작할게요!

목차 1. VBA 없이 데이터를 하나의 시트로 모으는 방법 2. 각 시트의 데이터 모으기 : VBA사용 3. 사용시 주의점 4. VBA 코드

​

*함께 알면 좋은 내용

[엑셀TIP] 여러 파일의 시트들 하나의 파일에 모으기

[엑셀TIP] 같은 파일 내 서로 다른 시트 동시에 보기

[엑셀TIP] VBA시작하기 : 개요, 옵션 설정

1. VBA 없이 데이터를 하나의 시트로 모으는 방법

예제

그림과 같은 파일이 있어요.

시트1~6까지 6개의 시트가 있구요,

​

각각 형식은 같지만 다른 데이터를 가지고 있는거죠.

​

그리고 이 여섯개의 시트로 나누어져 있는 데이터를

하나의 시트로 모으고 싶은거에요.

혹시나,,,, 어떻게, 똑같은 파일에 있는 시트들을 저렇게 볼 수 있는거지? 궁금하신 분들이 있으시다면, ​ [엑셀TIP] 같은 파일 내 서로 다른 시트 동시에 보기 위의 포스팅 참고하시면 되구요, ​ 귀찮으신 분들을 위해 결론만 말씀드리면, [보기]탭에 있는 [새 창], [모두 정렬] 기능을 사용하시면 돼요.

​

시트를 제대로 합치면 그림처럼 되겠죠?

​

​

사실 VBA없이 시트를 합치는 방법은 간단해요.

그저, 시트를 옮겨가면서

복사, 붙여넣기를 하면 됩니다.

​

너무,, 쉽지만,

시트가 많아질 경우

너무 너무 귀찮아진다는 단점이 있어요.

​

2. 각 시트의 데이터 모으기 : VBA사용

그래서 준비했어요.

시트가 아무리 많아도 한번에

데이터들을 모을 수 있는 방법이에요.

​

간단하게 방법만 촤라락 소개할게요!

​

① VBA로 들어갑니다.( Alt + F11 )

 

[개발 도구]탭에서 Visual Basic을 찾을 수 있는데요,

[개발도구]가 보이지 않으시는 분들은 설정을 해주셔야 해요.

관련 내용은 아래의 포스팅 참고해 주세요.

[엑셀TIP] VBA시작하기 : 개요, 옵션 설정

​

VBA 단축키 : ALT + F11

참고로, 단축키 [ Alt + F11 ] 을 누르면

그냥 들어갈 수 있으니, 귀찮으신 분들은

그냥 단축키를 사용해 주세요.

VBA 화면

위의 그림과 같은 화면을 확인 할 수 있어요.

​

​

② 아래의 파일을 다운받아주세요.

첨부파일

시트합치기.bas

파일 다운로드

VBA코드에요.

다운로드 받기가 불안하신 분들은,

본문 마지막에 코드를 올려드릴테니까요,

복사해서 사용하셔도 무방합니다.

​

③ 다운받은 파일을 열어주세요.( CTRL + M )

​

파일 가져오기 단축키

참고로, 단축키는 [ CTRL + M ]이에요.

​

생성된 모듈 ( 빨간색 사각형 부분)

​

파일을 가져오면, 그림처럼 모듈이 생성될거에요.

​

​

④ 파일을 저장해주세요.

파일을 저장해야 하는데요,

매크로 사용 통합문서(*.xlsm)

이때, 확장자를

Excel 매크로 사용 통합 문서(*.xlsm)으로 해주세요.

​

매크로 문서로 저장해야 매크로 사용이 가능합니다.

​

⑤ 매크로 창 열기( ALT + F8 )

이제, 매크로를 사용하기만 하면 되는데요.

VBA 창은 끄셔도 무방합니다.

켜두셔도 되구요.

이미 저장했기 때문에, 수정만 하지 않으시면

어떻게 하든 상관없어요.

​

엑셀 워크시트로 돌아와서,

[개발도구]탭에서 [매크로]를 눌러주세요.

​

매크로 창 단축키

매크로 창 단축키는 [ ALT + F8 ]이에요.

​

매크로 창 열기

​

⑥ 매크로 창에서 [실행]단추 누르기

매크로 창

매크로 창을 열면, [실행]단추를 볼 수 있는데요,

[실행]을 누르면 시트가 모아져요.

​

참고로, [편집]을 누르면 다시

VBA창으로 돌아가 있는 화면을 보실 수 있구요,

[삭제]는 하면 사용을 못하겠죠,,?

[옵션]은,, 단축키를 설정할 수 있는데요,

뭐,, 굳이 필요한 부분은 아니에요.

​

매크로 실행 결과

쨋든, [실행]을 눌러볼게요.

매크로를 실행한 결과,

'합쳐진 시트'라는 새로운 시트가 생성되었구요,

시트1~6의 데이터가 모아졌어요.

3. 사용시 주의점

사용시 주의할 점은요,

이따 VBA 코드를 보면서 다시 말씀드리겠지만,

코드가 짜여진 특성상,

각 시트의 내용은 A1열부터 내용이 채워져 있어야

정상적으로 동작을 합니다.

​

​

그리고, 당연한 말이겠지만

각 시트의 내용이 동일한 구조를 가지고 있어야

동일한 구조를 가지고 복사가 되겠죠?

​

데이터가 시트 중간중간에 내용이 빠져 있는건

상관이 없습니다만,

모아진 시트의 첫번째 행은

첫번째 시트의 내용만을 가져오구요,

예를 들어서,

시트1

시트2

시트1, 시트2의 데이터가 위의 그림과 같다면,

시트1의 1열을 가져오게 되므로,

​

데이터가 맞지 않는 경우

합쳐진 시트는 위의 그림처럼 됩니다.

시트2에 있는 비고1,2,3이 없어졌죠?

​

​

​

추가적으로, 전혀 연속되지 않는 데이터는 가져오지 않아요.

예를 들어서,

시트1

시트2

시트6

예를 들어서 시트 1,2,6이 위의 그림과 같다면,

시트6에 뜬금없이 있는

'갑자기'라는 글자는 나오지 않는거죠.

​

해당 내용들을 주의하시면,

어렵지 않게 사용하실 수 있겠죠?

4. VBA 코드

VBA 코드에요.

Sub 시트합치기() ​ Dim i As Integer On Error Resume Next ​ Sheets(1).Select '첫번째 시트 선택 Worksheets.Add '시트 추가 Sheets(1).Name = "합쳐진 시트" '추가한 시트의 이름 Sheets(2).Activate '두번째 시트로 이동 Range("A1").EntireRow.Select 'A1의 행을 Selection.Copy Destination:=Sheets(1).Range("A1") '첫번째 시트의 A1으로 복사 ​ For i = 2 To Sheets.Count '다음 시트부터 반복 Sheets(i).Activate Range("A1").Select Selection.CurrentRegion.Select '값이 있는 셀 선택 Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select '선택에서 1행 제외 Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2) '첫번째 시트로 붙여넣기 ​ Next ​ End Sub

어렵지 않죠?

참고로, 주석은 제가 블로그에서 적은거라서요,,

파일을 다운받으시면 주석은 없어요,,!

​

​

삽입 - 모듈

추가적으로,

VBA창에서 그냥 입력하시려면,

'삽입 - 모듈'을 누르시고 코드를 입력하면 됩니다.

---

넵, 여기까지

하나의 파일에 있는 시트들

하나의 시트로 모으기,

시트 취합하기,

여러 시트들 속의 데이터 취합하기,

시트 합치기,

데이터 모으기 였습니다.

​

마지막으로, 동영상을 보면서 마치도록 할게요!