[VBA 매크로] 매크로로 시트 목록 생성하기 (기초버전)

 

저는 엑셀 파일에 시트가 많은 경우 별도의 목차시트를 만들어서 시트 목록을 만들고, 시트별로 하이퍼링크도 걸어서 관리하는데요. 이번 글에서는 매크로로 시트 목록을 생성하는 기초적인 방법을 소개하겠습니다. 완성된 예시 파일을 다운받아 참고해주세요. 

 

시트목록생성기초버전.xlsm

0.02MB

 

예시 파일 상황

▲ 첫 번째 시트의 시트명을 '목차'로 설정하고, 목차 시트의 A2셀부터 시트 목록을 생성하려고 합니다. 즉, 매크로를 실행했을 때, A2셀부터 A4셀까지 TEST1~TEST3이 입력되는 것이 목표입니다. 

 

 

시트 목록생성 매크로 VBA 코드

▲ 매크로 코드 전문은 업로드한 엑셀 파일이나 텍스트 파일에서 그대로 복사하실 수 있습니다.

Sub 시트목록().txt

0.00MB

 

 

<주요 구문 해석>

1) Dim i as Integer 

변수 'i'를 Integer이라는 숫자(정수) 형식의 변수로 선언(설정)하는 구문입니다. 'i'가 어떤 변수인지 선언하지 않고 코드를 짜면 오류가 발생하여 매크로가 정상적으로 실행되지 않습니다. 

 

2) For i =2 To Worksheets.Count ~(명령구문)~ Next i

i 가 2부터 시작해서 Worksheets.Count가 될 때까지 명령구문을 반복하라는 For ~ Next 순환문입니다.  

For i =1이 아닌 For i= 2인 이유는, 첫 번째 시트는 목차시트이므로 시트목록에 제외시키기 위해서입니다.

 

Worksheets.Count는 현재 활성화된 엑셀 파일의 시트 갯수를 뜻합니다. 예시파일은 시트가 4개이기 때문에 Worksheets.Count는 4를 의미하며, 시트 갯수가 바뀌면 자동적으로 Worksheets.Count 값도 변동합니다.

 

3) Sheets("목차").Range("A" & i) = Sheets(i).Name

Sheets("목차").Range("A" & i) 는 시트명이 '목차'인 시트의 A열 i행을 의미합니다. 

Sheets(i).Name은 현재 활성화된 엑셀 파일의 i번째 시트의 이름을 의미합니다.

 

따라서, Sheets("목차").Range("A" & i) = Sheets(i).Name 는 시트명이 '목차'인 시트의 A열 i행에 i번째 시트의 이름을 입력하라는 의미입니다. 

 

728x90

 

매크로 실행결과 및 시트 갯수 감소 시 문제점 

▲ 예시 파일에는 매크로 실행 단추도 설정해 놓은 상태입니다. '시트목록생성' 단추를 클릭한 결과, 위의 사진처럼 A2 ~ A4 셀에 각각 TEST1~TEST3이 입력됐습니다. 

 

 

▲ 위의 사진처럼 시트 갯수가 늘어나고, 이름이 바뀌어도 자동으로 반영됩니다. 

 

 

▲ 하지만 시트 갯수가 감소했는데 원래 데이터를 삭제하지 않고 매크로를 실행하면 매크로는 A2~A4에 정상적으로 시트목록을 생성했는데, A5에는 기존에 입력된 데이터가 그대로 남아있어 수정이 필요합니다.

 

이 문제를 해결하는 방법으로는 대표적으로 두 가지가 있습니다. 

1) 매크로를 실행하기 전에 기존에 입력된 데이터를 직접 지우는 방법

2) 기존에 데이터를 알아서 지우도록 VBA 코드를 작성하는 방법

 

두 번째 방법에도 여러가지 방법이 있기 때문에 향후에 Range 변수를 선언하는 방법, 셀의 내용을 지우는 코드 등을 소개하면서 다루도록 하겠습니다.