엑셀 VBA: 시트 목차 자동으로 만들기 (하이퍼링크 이동 메뉴)

엑셀 작업하다 보면
어느 순간 시트가 점점 늘어납니다.

 

1월 매출, 2월 매출, 3월 매출…
거기에 지점별, 부서별 시트까지 붙으면
어느새 시트가 20개, 30개를 훌쩍 넘기죠.

 

이럴 때
“12월 지출 시트 좀 보여줘요”
라는 말이 나오면,
아래쪽 시트 탭을 한참 넘기다가
괜히 혼자 식은땀 흘린 적, 다들 한 번쯤 있으실 겁니다.

 

그래서 정리해 둔 게
엑셀 파일 맨 앞에 ‘목차’ 시트를 자동으로 만들어 주는 매크로입니다.
버튼 한 번 누르면,
각 시트로 바로 이동할 수 있는 링크가 한 번에 생깁니다.

---

이런 경우에 특히 유용합니다

실무에서 은근히 자주 씁니다.

• 시트가 많아서 찾기 힘들 때
• 상사나 거래처에 보고용 파일을 제출할 때
• “이 파일 뭐부터 보면 되나요?”라는 질문을 자주 받을 때
• 엑셀 파일을 조금이라도 깔끔하게 보이고 싶을 때

목차 하나만 있어도
파일 전체 인상이 꽤 달라집니다.

---

시트 목차 매크로는 이렇게 동작합니다

이 매크로를 실행하면,

• 엑셀 파일 맨 앞에 ‘목차’ 시트를 만들고
• 현재 파일에 있는 모든 시트 이름을 정리해서 적어주고
• 각 시트로 바로 이동할 수 있는 하이퍼링크를 자동으로 생성합니다

이미 ‘목차’ 시트가 있으면
기존 건 지우고 새로 만들어주기 때문에
시트가 추가되거나 이름이 바뀌어도 다시 만들기 쉽습니다.

---

VBA 매크로 사용 방법

1. 엑셀에서 Alt + F11 눌러 VBA 편집기 실행
2. 상단 메뉴 [삽입] → [모듈] 클릭
3. 아래 코드 그대로 복사해서 붙여넣기
4. 엑셀로 돌아와 Alt + F8 → CreateSheetIndex 실행

---

시트 목차(하이퍼링크) 생성 VBA 전체 코드

 

Sub CreateSheetIndex()

    Dim ws As Worksheet
    Dim IndexSheet As Worksheet
    Dim i As Long
    Dim SheetName As String

    Application.ScreenUpdating = False

    ' 기존 목차 시트가 있으면 삭제
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("목차").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' 맨 앞에 목차 시트 생성
    Set IndexSheet = Worksheets.Add(Before:=Worksheets(1))
    IndexSheet.Name = "목차"

    ' 제목 표시
    With IndexSheet.Range("B2")
        .Value = "엑셀 시트 목차"
        .Font.Bold = True
        .Font.Size = 14
    End With

    ' 시트 목록 작성
    i = 4

    For Each ws In ActiveWorkbook.Worksheets

        If ws.Name <> IndexSheet.Name Then
            SheetName = ws.Name

            IndexSheet.Cells(i, "B").Value = SheetName

            IndexSheet.Hyperlinks.Add _
                Anchor:=IndexSheet.Cells(i, "B"), _
                Address:="", _
                SubAddress:="'" & SheetName & "'!A1", _
                TextToDisplay:=SheetName

            i = i + 1
        End If

    Next ws

    IndexSheet.Columns("B").AutoFit
    Application.ScreenUpdating = True

    MsgBox "시트 목차가 생성되었습니다.", vbInformation, "완료"

End Sub

---

같이 쓰면 좋은 팁 (목차로 돌아가기)

목차를 만들어 놓으면,
각 시트에서 다시 목차로 돌아오는 것도 중요합니다.

굳이 매크로를 더 만들 필요는 없고,

• Ctrl + PageUp / PageDown
  → 시트 이동 기본 단축키
• 혹은
  각 시트 A1 근처에
  목차로 이동 같은 글자를 써두고
  우클릭 → 링크 → 현재 문서 → 목차

이렇게 하나만 만들어 둬도 충분히 편합니다.

---

마무리

엑셀에서
정리 안 된 시트는 그냥 데이터 덩어리지만,
목차 하나만 있어도 문서처럼 보이기 시작합니다.

 

보고용 파일이나 공유용 파일이라면
이 매크로 한 번만 돌려도
“엑셀 정리 잘하네”라는 말 듣기 딱 좋습니다.

 

앞에서 소개한
파일 합치기, 쪼개기, PDF 변환, 이름 변경, 메일 발송, 백업 매크로와
같이 써보시면 더 효과가 큽니다.

 

다음에도
실무에서 바로 써먹을 수 있는 엑셀 자동화 팁 있으면
계속 정리해서 올려보겠습니다. 감사합니다!