엑셀 Excel VBA 매크로 워크 시트 목차 만들기

엑셀 작업 중 데이터 입력이 많은 경우 시트를 추가해서 만들거나
분류로 인해 시트별로 작업하기도 합니다.
여러 시트가 생성된 경우 목차를 만들고 해당 목차를 생성하고 링크를 설정해 놓으면
편한데요. 시트가 몇 개 없으면 시트 명을 입력 후 하나씩 링크를 설정하면 되지만

작업한 시트가 많은 경우 번거로운 작업이 됩니다.
VBA 매크로를 이용하면 한 번에 목차를 만들 수 있는데요, 방법을 알아보겠습니다.

[관련 포스티]
엑셀에서 목차를 직접 생성하는 방법입니다. 아래를 참고하세요

엑셀 목차 만들기 하이퍼링크로 연결된 시트 이동

엑셀 목차 만들기 하이퍼링크로 연결된 시트 이동 여러 문서 편집 프로그램은 목차를 첫페이지에 입력해서 작업 진행을 합니다. 워드나 한글 프로그램은 목록 페이지를 종종 볼 수 있는데요. 엑

loveuceci.tistory.com

간단한 샘플을 준비했습니다.

아래 샘플에 여러 시트를 생성했고 생성한 시트를 VBA를 실행해서 자동으로 목차를 생성할 겁니다.
목차 화면 같은 느낌을 주기 위해 상단 메뉴 중 [보기]를 선택 후 [눈금선]을 체크 해제했습니다.
목차가 생성될 시작 셀은 "b6"입니다.

개발 도구를 추가하기 위해 상위 메뉴 중 [파일]을 선택 후 [옵션]을 선택합니다.

Excel 옵션 창이 열리면 좌측 메뉴 중 [리본 사용자 지정]을 선택합니다.
우측 메뉴가 나오면 리본 메뉴 사용자 지정에 [개발 도구]에 체크 후 [확인] 버튼을 클릭해 주세요

이제 상위 메뉴 중 [개발도구]-[Visual Basic]을 클릭합니다.

Visual Basic이 실행 하면 상위 메뉴 중 [삽입]-[모듈]을 선택합니다.

편집 창이 열리면 아래 명령어를 입력합니다.

Sub CreateIndex()

    Dim indexList As Worksheet
    Dim wkSheet As Worksheet
    Dim i As Integer

    Set indexList = ActiveSheet

    For Each wkSheet In ThisWorkbook.Worksheets

        If wkSheet.Name <> indexList.Name Then

            With indexList
                .Range("B6").Offset(i).Value = wkSheet.Name
                .Range("C6").Offset(i).Value = "-------------------------"
                .Hyperlinks.Add Anchor:=.Range("D6").Offset(i), _
                                Address:="", _
                                SubAddress:=wkSheet.Name & "!A1", _
                                TextToDisplay:="바로가기"
            End With

            i = i + 1

            With wkSheet
                .Rows(1).Interior.Color = RGB(220, 220, 220)
                .Hyperlinks.Add Anchor:=.Range("A1"), _
                                Address:="", _
                                SubAddress:=indexList.Name & "!A1", _
                                TextToDisplay:=wkSheet.Name
            End With
        End If
    Next
End Sub

내용 중간에 시작될 셸 위치가 나옵니다. "B6"는 시트 명을 "C6"에는 "--------------------" 기호를
마지막으로 "D6"에는 바로가기 하이퍼링크를 삽입합니다.

다른 위치를 원하시면 값을 변경하셔도 됩니다.
실행은 Visual Basic Application에서 해도 되고 엑셀에서 매크로 실행을 하셔도 됩니다.
상위 [사용자 정의 폼 실행]을 (재생버튼) 눌러줍니다. 오류가 없으면 잘 된 겁니다.