안녕하세요.
여러 시트에 흩어져 있는 데이터를 한 시트에 복사하는 방법을 VBA로 만들어 보겠습니다.
먼저 매크로를 이용해 기본적인 코드를 만들고 필요한 부분을 조금 고쳐 마무리합니다.
실습 파일 다운로드
완성 파일 다운로드
선택한 시트의 데이터를 단축키로 한꺼번에 범위 선택하고, 총괄 시트의 데이터 나타내는 영역 아래쪽에 이어서 나타나도록 매크로를 만듭니다.
A대리점 시트 [A4] 셀에 커서를 두고 [매크로 기록]을 시작합니다.
[개발 도구]-[매크로 기록]을 누릅니다.
[매크로 이름]은 데이터복사, 바로 가기 키는 <Ctrl + Shift + M>으로 지정합니다.
[확인]을 눌러 기록을 시작합니다.
[A4] 셀에서 <Ctrl + 화살표 오른쪽>을 누르고 다시 <Ctrl + 화살표 아래쪽>을 눌러 범위 선택합니다.
선택된 범위에서 마우스 오른쪽 단추를 눌러 [복사]를 선택합니다.
총괄 시트를 선택하고, 붙여넣기 합니다.
[개발 도구]-[기록 중지]를 선택합니다.
[개발 도구]-[Visaul Basic]를 눌러 VBE를 실행하고 작성된 매크로 코드를 확인합니다.
만들어진 코드를 보니 아직 익숙하지 않지만 매크로를 만들 때 뭘 어떻게 선택했는지를 떠올리면 각 코드들이 뭘 의미하는지 대강 어림짐작할 수 있죠?
정확하지 않을 수 있지만 어설프게 나마 코드를 대략 보는 법을 익히는 것이 중요합니다.
이런 과정을 거쳐 복잡하고 어려운 작업도 만들어 낼 수 있는 방법을 배우게 되기 때문입니다.
개체나 메서드, 속성이 여럿 나열되어 있습니다.
Range, Selection, Sheets, ActiveSheet는 개체이고, Select, End, Copy, Paste는 메서드이며 End는 속성입니다.
당장은 무슨 뜻이고 어떤 동작을 하는지 몰라도 됩니다.
우리가 원하는 동작이 실행된다면 그것으로 충분하죠.
차차 시간이 지나고 매크로 작업을 하다 보면 알게 됩니다.
그런데 코드 중 뭔가 이상한 부분, 또는 잘못된 것 같은 부분이 있습니다.
Range("A2").Select 라는 부분이죠.
총괄 시트를 선택해 붙여넣기 하기 전에 붙여 넣을 위치를 선택하는 부분입니다.
그런데 [A2] 셀로 딱 고정되어 있으면 처음에 한 번 붙여 넣을 때는 괜찮습니다.
다음 번에 다른 시트 데이터를 복사해 붙여 넣게 되면 원래 복사 되어 있는 데이터를 덮어쓰게 됩니다.
만드려는 의도대로 올바르게 실행되는 것이 아닙니다.
실제 어떻게 동작할지 확인하기 위해 엑셀로 돌아가 매크로를 실행해 보죠.
VBE 창에서 <Alt + F11>을 누르면 엑셀 창으로 돌아갑니다.
B대리점 시트 [A4] 셀에 커서를 두고 <Ctrl + Shift + M>을 눌러 매크로를 실행합니다.
예상과 달리 별 이상 없이 제대로 실행된 것 같죠?
총괄 시트를 잘 보면 잘못된 것을 알 수 있습니다.
아까 앞에서 A대리점 데이터를 복사해 붙여넣기 했는데, 그게 없어졌죠?
매크로에 붙여넣을 위치가 [A2] 셀로 고정되어 있어 덮어 쓴 겁니다.
덮어쓰지 않고 제대로 동작하려면 어떤 식으로 동작해야 할까요?
1. 총괄 시트로 왔을 때 일단 [A2] 셀을 선택합니다.
2. <Ctrl + 화살표 아래쪽>를 눌러 입력된 데이터 제일 끝으로 이동합니다.
3. <화살표 아래쪽>를 눌러 아래쪽으로 한 칸만 내립니다.
4. 붙여넣기를 합니다.
이렇게 동작하도록 코드를 수정하면 됩니다.
<Ctrl + Shift + 화살표 아래쪽>을 눌렀을 때 매크로 코드는 있지만, <Ctrl + 화살표 아래쪽>를 눌렀을 때 만들어지는 매크로는 모르죠?
이럴 땐 다시 매크로를 기록해 보면 됩니다.
<Ctrl + 화살표 아래쪽>을 누르는 매크로를 만들고 코드를 확인해 보면 어떤 식으로 적어야 하는지 알 수 있죠.
힘들게 검색하지 않아도 됩니다.
물론 어떨 땐 인터넷 검색하는 것이 더 빠르게 필요한 코드를 확인하는 방법이 되기도 합니다.
총괄 시트 [A2] 셀에 커서를 두고 [개발 도구]-[매크로 기록]을 누릅니다.
매크로를 만들어서 코드만 확인할 계획이라서 매크로 이름을 바꿀 필요도 없습니다.
매크로 이름은 기본값 그대로 두고 [확인] 단추를 누릅니다.
<Ctrl + 화살표 아래쪽>을 누르고 다시 <화살표 아래쪽>을 한 번 눌러 [기록 중지]를 누릅니다.
[Visual Basic]을 눌러 만들어진 매크로를 확인해 보죠.
물론 <Alt + F11>을 눌러도 VBE 창으로 전환됩니다.
<Ctrl + 화살표 아래쪽>을 누른 코드가 Selection.End(xlDown).Select 입니다.
그런데 두번째 Range("A14").Select 코드는 문제가 있습니다.
그냥 [A14] 셀을 선택하라는 코드입니다.
지금은 [A14] 셀이 다음 번 데이터를 붙여 넣기할 위치의 셀이지만, 다음 번에는 달라야 합니다.
[A14] 셀로 절대 고정되는게 아니라 상대적 위치의 셀을 선택하는게 필요합니다.
매크로를 기록할 때 [상대 참조로 기록]을 미리 누르고 매크로를 기록하면 현재 셀 위치를 기준으로 상대적인 위치로 이동하게 할 수 있습니다.
쓸모 없는 매크로2를 범위 선택하고 <Del>을 눌러 코드를 지웁니다.
<Alt + F11>을 눌러 다시 엑셀 창으로 갑니다.
[A2] 셀을 선택하고, [상대 참조로 기록]을 누릅니다.
다시 [매크로 기록]을 누릅니다.
이번에도 임시로 한 번 기록하고 없애버릴 매크로이기 때문에 매크로 이름은 바꾸지 않고 그냥 [확인]을 누릅니다.
<Ctrl + 화살표 아래쪽>을 누르고 다시 <화살표 아래쪽>을 누릅니다.
[기록 중지]를 누릅니다.
<Alt + F11>을 눌러 VBE를 실행합니다.
첫번째 코드는 아까와 같지만 두번째는 완전히 다릅니다.
상대 참조로 셀을 선택하는 코드입니다.
참고로 Offset은 속성입니다.
Offset 괄호에 첫번째 숫자는 행방향으로 움직일 거리이고, 두번째 숫자는 열방향 거리입니다.
행방향 숫자가 1이니 다음 행으로 옮겨가는 코드인거죠.
매크로3의 코드 두줄을 복사해서 데이터복사 매크로의 Range("A2").Select 코드 아래 붙여 넣습니다.
이제 매크로가 제대로 동작하는지 다시 확인해 보죠.
<Alt + F11>을 누릅니다.
C대리점 시트 [A4] 셀에 커서를 둡니다.
단축키 <Ctrl + Shift + M>을 눌러 매크로를 실행합니다.
이번엔 제대로 실행된 것 같죠?
앞에 복사했던 B대리점 데이터 아래 방금 복사한 C대리점 데이터가 붙어 있습니다.
이렇게 간단하게 매크로만으로 쉽게 구현하다니 뿌듯하지 않습니까?
그러고 보니 그 사이 저장을 한 번도 하지 않고 계속 작업을 하고 있었군요.
저장부터 먼저 하죠.
만든 매크로를 실행하기 전에 미리 저장해 두는게 중요합니다.
혹시 매크로를 실행하는 중에 오류가 생기면 엑셀 프로그램이 비정상 종료되고 저장하지 않은 모든 내용이 사라지는 경우가 종종 있기 때문입니다.
[파일]-[다른 이름으로 저장]을 누릅니다.
[파일 형식]을 [Excel 매크로 사용 통합 문서(*.xlsm)]으로 바꾸고 [저장]을 누릅니다.
처음부터 다시 제대로 동작하는지 확인을 해 보죠.
총괄 시트를 선택합니다.
[A2] 셀에 커서를 두고 <Ctrl + Shift + 화살표 오른쪽>을 누르고 다시 <화살표 아래쪽>을 눌러 데이터 범위를 지정합니다.
선택 범위에서 마우스 오른쪽 단추를 눌러 [삭제]를 선택합니다.
[행 전체]를 선택하고 [확인]을 누릅니다.
테스트하기 위한 준비가 다 되었습니다.
A대리점 시트 [A4] 셀에 커서를 두고 <Ctrl + Shift + M>을 눌러 데이터복사 매크로를 실행합니다.
여기까지 따라서 그대로 연습하고 있다면 놀라지 마세요.
오류가 납니다.
[디버그]를 누릅니다.
노란색으로 표시된 코드 부분에서 오류가 났다는 표시입니다.
앞으로도 계속 만나게 될 모습입니다.
VBA를 제대로 공부한다면 꼭 오류를 만나게 됩니다.
오히려 오류가 나지 않는다면 뭔가 이상한 거죠.
왜 이런 오류가 났는지 확인해 보죠.
표준 도구모음의 [실행] 단추 옆 [재설정] 단추를 누릅니다.
이 오류가 날 것을 저는 예상하고 있었습니다. ^^
<Alt + F11>을 눌러 엑셀 창으로 전환합니다.
총괄 시트 A열 제일 마지막 셀에 커서가 놓여져 있는게 보이죠?
시트의 제일 마지막 열에서 그 밑 열로 옮기려 했기 때문에 오류가 생긴 겁니다.
앞에서 테스트할 때는 오류가 나지 않았죠.
이유는 총괄 시트 [A2] 셀에 다른 시트에서 복사한 데이터가 있었기 때문입니다.
제일 처음에 매크로를 실행하면 [A2] 셀에는 아무런 데이터도 없습니다.
여기서 <Ctrl + 화살표 아래쪽>을 누르면 시트 제일 마지막 행으로 이동하게 되죠.
이 문제는 총괄 시트에 복사한 내용이 없을 때 딱 한 번 생기는 문제입니다.
이런 경우가 종종 있고, 이 강좌를 시작할 때부터 생각하고 있던 부분이었습니다.
물론 해결책도 이미 알고 있습니다.
매크로만으로는 해결할 수 없고 VBA 코드를 써야 합니다.
총괄 시트 [A2] 셀에 데이터가 있을 때만 <Ctrl + 화살표 아래쪽>을 누르게 하는 거죠.
<Alt + F11>을 눌러 다시 VBE로 갑니다.
조건문 If로 <Ctrl + 화살표 아래쪽>을 누르는 코드와 다른 행으로 옮기는 코드를 둘러 쌉니다.
이것이면 해결 됩니다.
다시 <Alt + F11>을 눌러 엑셀 창으로 이동하고, A대리점 시트 [A4] 셀에 커서를 두고 <Ctrl + Shift + M>을 눌러 매크로를 실행합니다.
제대로 동작해서 A대리점 데이터를 복사해 왔죠.
B대리점 시트와 C대리점 시트에서도 매크로를 실행해 잘 동작하는지 확인해 봐도 됩니다.
주의할 점은 매크로를 실행할 때 꼭 대리점 시트 [A4] 셀에 두고 시작해야 합니다.
각 대리점 시트 [A4] 셀부터 데이터가 입력되어 있기 때문입니다.
VBA 코드에서 다른 셀에서 실행하는지, 아닌지를 확인하는 코드를 작성하지 않았습니다.
만약 정해진 위치에서 실행하지 않는 경우도 있다면 그럴 경우에 대비한 코드도 작성해야 합니다.
그러면 매크로 내용이 아주 복잡해 집니다.
그래서 이 강좌에선 '꼭 각 대리점 시트 [A4] 셀에 커서를 두고 매크로를 실행하세요'라고 이야기만 하는 겁니다.
이제 모든 작업이 끝났습니다.
완성 코드입니다.
Sub 데이터복사()
'
' 데이터복사 매크로
'
' 바로 가기 키: Ctrl+Shift+M
'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("총괄").Select
Range("A2").Select
If Range("A2") <> "" Then
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
End If
ActiveSheet.Paste
End Sub
VBE 창에서 코드 확인을 위해 만들었던 매크로3 코드는 지우고 통합문서를 저장합니다.
매크로 만들기, VBA 코딩 어렵지 않죠?
필요하신 분들께 도움이 되길 바라며 강좌는 마치겠습니다.
수고하셨습니다.
'매크로&VBA' 카테고리의 다른 글
| 엑셀 매크로와 VBA 이야기 (0) | 2020.05.23 |
|---|---|
| 여러 시트에 데이터를 하나의 시트로 합치는 간단한 VBA (0) | 2020.05.21 |
| 엑셀 매크로&VBA] 여러 시트 내용을 조회하고 수정하는 간단한 VBA (0) | 2020.04.12 |
| 엑셀 매크로&VBA] 고급 필터 매크로를 보완하는 VBA(변수와 InputBox 함수) (0) | 2020.04.02 |
| 엑셀 매크로&VBA] 고급 필터 매크로(조건을 변경할 때마다 결과 값이 자동으로 나오도록 매크로 설정하기) (2) | 2020.03.23 |