안녕하세요, 이충욱 강사입니다.
구독자 윤**님의 질문을 바탕으로 구성한 VBA 실무 강좌입니다.
질문 내용은 아래와 같습니다.
제가 약 200개 회사의 자료를 분석해서 작성해야 하는데요
옆 sheet의 사례처럼 각 회사별로 동일할 형태의 데이터를 각각 입력하면 sheet의 수가 200개 정도가 되어야 하거나 아니면 1파일로 할 경우 200개 파일로 나누어 작성해야 합니다
데이터를 입력하는 노력은 같지만 각 회사를 찾는 것에는 의외로 번거롭고 애로사항이 있을 것 같습니다
혹시 하나의 시트에 다음과 같이 회사명을 선택하는 버튼을 만들어 해당 버튼에서 회사만 선택해서 각 회사별 데이터를 입력/수정 할 수 있는 방법이 있다면 알고 싶습니다
해서 강사님 책자랑 자료 등을 찾아보아도 방법을 잘 모르겠습니다.
실습용으로 데이터를 구성했습니다.
처리할 작업은 아래와 같습니다.
1. [작업] 시트에서 회사명을 선택하고 [조회] 버튼을 누르면 회사명에 맞는 시트에서 영업이익(t)에서 이자비용까지 조회하여 표시하도록 하려고 합니다.
2. 반대로 회사를 조회한 후 값을 변경한 다음 [수정] 버튼을 누르면 해당 시트에 값이 변경되도록 처리하려고 합니다.
실습 파일 다운로드
완성 파일 다운로드
이전 강좌 [INDIRECT 함수를 사용하여 특정 시트의 값 조회하기 https://hantip.net/276]는 특정 시트의 내용을 조회하는 방법만 처리했습니다.
조회는 가능하지만 질문하신 내용처럼 입력과 수정은 할 수 없어 VBA를 사용해 조회 뿐만 아니라 입력과 수정까지 가능하도록 처리해보겠습니다.
조회와 수정 역할을 하는 매크로 두 개를 만들겠습니다.
단축키 <Alt + F11>을 눌러 VBE를 실행합니다.
[삽입]-[모듈]을 선택합니다.
'모듈'은 매크로를 담는 파일입니다.
저장하면 엑셀 통합문서에 포함되어 저장됩니다.
[삽입]-[프로시저]를 선택합니다.
'프로시저(Procedure)'가 매크로입니다.
[프로시저 추가] 대화상자가 실행되면 [이름] 입력창에 '조회'를 입력하고 [확인]을 누릅니다.
다른 설정은 기본값으로 둡니다.
같은 방법으로 '수정' 프로시저도 만듭니다.
조회와 수정 매크로가 작성되었습니다.
1. 조회 매크로 작성
조회 매크로에 커서를 두고 <Tab>키를 눌러 들여쓰기 한 다음 아래와 같이 작성합니다.
시트명 = Range("A1")
시트명 변수에 [작업] 시트 [A1] 셀의 목록에서 선택된 회사명을 저장하라는 명령입니다.
<Enter> 를 두 번 눌러 한 줄 띄운 다음 아래와 같이 코드를 입력합니다.
코드를 붙여 길게 쓰면 내용을 파악하기 힘들기 때문에 줄을 띄워 가독성 높여줍니다.
Range("B2") = Sheets(시트명).Range("B2")
Range("B2")는 현재 시트의 [B2] 셀을 의미하고, Sheets(시트명).Range("B2")는 A사 시트의 [B2] 셀을 의미합니다.
[A사] 시트 [B2] 셀의 값을 현재 선택되어 있는 [작업] 시트 [B2] 셀에 대입하라는 명령입니다.
Sheets는 개체(Object) 또는 컬렉션(Collection)입니다.
Sheets는 엑셀 시트를 나타내는데, 하나의 통합문서에 시트가 한 개가 있을 수도 있지만 여러 개 있을 수 있습니다.
Sheets(1)이라고 하면 첫번째 시트를 의미하고, 시트 탭에 순서대로 나열된 번호로 지정할 수 있습니다.
그리고 Sheets("Sheet1") 형태로 시트명을 직접 넣어 시트를 선택할 수도 있습니다.
한 개는 개체이고, 여러 개의 개체 모음을 컬렉션이라고 합니다.
방금 작성한 코드를 복사해서 아래와 같이 셀 주소를 수정합니다.
Public Sub 조회()
시트명 = Range("A1")
Range("B2") = Sheets(시트명).Range("B2")
Range("B3") = Sheets(시트명).Range("B3")
Range("B4") = Sheets(시트명).Range("B4")
Range("B5") = Sheets(시트명).Range("B5")
Range("B6") = Sheets(시트명).Range("B6")
Range("E2") = Sheets(시트명).Range("E2")
Range("E3") = Sheets(시트명).Range("E3")
Range("E4") = Sheets(시트명).Range("E4")
Range("E5") = Sheets(시트명).Range("E5")
Range("E6") = Sheets(시트명).Range("E6")
End Sub
2. 수정 매크로 작성
조회 매크로의 내용과 같고, 위치만 뒤집으면 됩니다.
Public Sub 수정()
시트명 = Range("A1")
Sheets(시트명).Range("B2") = Range("B2")
Sheets(시트명).Range("B3") = Range("B3")
Sheets(시트명).Range("B4") = Range("B4")
Sheets(시트명).Range("B5") = Range("B5")
Sheets(시트명).Range("B6") = Range("B6")
Sheets(시트명).Range("E2") = Range("E2")
Sheets(시트명).Range("E3") = Range("E3")
Sheets(시트명).Range("E4") = Range("E4")
Sheets(시트명).Range("E5") = Range("E5")
Sheets(시트명).Range("E6") = Range("E6")
End Sub
매크로는 모두 만들었습니다.
만드는 중간중간 제대로 동작하는지 확인해 보는 것도 좋습니다.
<Alt + F11>키를 눌러 엑셀 창으로 돌아가서 [개발 도구]-[컨트롤]-[삽입]을 선택합니다.
[양식 컨트롤] 중 [단추(양식 컨트롤)]을 선택합니다.
[B1]과 [C1] 셀에 걸쳐 드래그한 뒤 마우스 버튼을 놓으면 매크로 지정 대화상자가 나타납니다.
조회 매크로를 선택하고 [확인] 단추를 누릅니다.
단추 위에 표시된 '단추 1' 텍스트를 지우고 '조회'로 수정합니다.
조회 단추에서 마우스 오른쪽 단추를 누르면 나타나는 빠른 메뉴에서 복사를 누릅니다.
바로 옆에 붙여넣기를 하고 '수정'으로 단추명을 변경합니다.
마우스 오른쪽 단추를 눌러 [매크로 지정]를 선택합니다.
수정 매크로를 선택하고 [확인] 단추를 누릅니다.
[A1] 셀에서 C사를 선택하고, 조회 단추를 눌러 봅니다.
C사 시트의 데이터가 가져와 집니다.
[B2] 셀의 값을 50으로 변경한 뒤 수정 단추를 눌러 봅니다.
C사 시트로 이동해 보면 [B2] 셀의 값이 50으로 변경되어 있습니다.
제대로 동작하는지 확인하는 작업도 마무리 되었습니다.
매크로가 포함된 엑셀 파일이라서 현재 파일 형식을 Excel 매크로 사용 통합 문서로 바꿔 저장합니다.
[파일]-[다른 이름으로 저장]을 선택하고 파일 형식에서 [Excel 매크로 사용 통합 문서]을 선택해서 [저장]을 누릅니다.
처리해야 할 내용이 모두 마무리 되었습니다.
수고하셨습니다.
'매크로&VBA' 카테고리의 다른 글
| 여러 시트에 데이터를 하나의 시트로 합치는 간단한 VBA (0) | 2020.05.21 |
|---|---|
| 시트의 내용을 다른 시트에 한꺼번에 간단히 복사하는 VBA (1) | 2020.05.19 |
| 엑셀 매크로&VBA] 고급 필터 매크로를 보완하는 VBA(변수와 InputBox 함수) (0) | 2020.04.02 |
| 엑셀 매크로&VBA] 고급 필터 매크로(조건을 변경할 때마다 결과 값이 자동으로 나오도록 매크로 설정하기) (2) | 2020.03.23 |
| 엑셀 매크로&VBA] 매크로를 보완하는 VBA (2) | 2020.03.23 |