엑셀 시트 목차 만들기

 엑셀 시트들 목차 만들어주기

 1) 엑셀 개발자 모드 > Visual Basic

 2) 모듈삽입

 3) 아래 소스 복사 후 저장

 Sub Create_TOC_InWorkbook()

On Error GoTo ErrOccered

'1. Variable Declaration

Dim iCnt As Integer

Dim Sht As Worksheet, TocSht As Worksheet

Dim ShtName As String

iCnt = 2

'2. TurnOff Screen updating and Events

With Application

    .ScreenUpdating = False

    .EnableEvents = False

End With

'3. Delete the 'TOC' WorkSheet if it exists

Application.DisplayAlerts = False

    On Error Resume Next

    ActiveWorkbook.Sheets("TOC").Delete

Application.DisplayAlerts = True

'4. Add a new WorkSheet and name as 'TOC'

With ActiveWorkbook

    Set TocSht = .Sheets.Add(After:=.Sheets(.Sheets.Count))

    TocSht.Name = "TOC"

End With

'5. Loop through each WorkSheet in the workbook to create TOC

For Each Sht In ThisWorkbook.Worksheets

    If Sht.Name <> "TOC" Then

        ShtName = Sht.Name  'Worksheet Name

        

        '5.1 Create hyperlink in TOC Worksheet

        With Sheets("TOC")

            .Activate

            .Range("B" & iCnt).Select

            .Hyperlinks.Add Anchor:=Selection, Address:="", _

             SubAddress:=ShtName & "!A1", TextToDisplay:=iCnt - 1 & ". " & ShtName

        End With

        

        '5.2 Create Back Link to all the Worksheets in the Workbook

        With Sheets(ShtName)

            .Activate

            .Hyperlinks.Add Anchor:=Range("A1"), Address:="", _

                SubAddress:="TOC!A1", TextToDisplay:="Back to TOC"

            .Range("A1").EntireColumn.AutoFit

        End With

        

        'Increment iCnt value for next row in TOC Worksheet

        iCnt = iCnt + 1

    End If

Next

'6. Formating the 2nd Column in TOC Sheet

With Sheets("TOC")

    .Activate

    .Range("B1") = "Table of Contents"

    .Range("B1").Font.Size = 16

    .Range("B1").EntireColumn.AutoFit

    .Columns("B:B").HorizontalAlignment = xlLeft

End With

ErrOccered:

'7. TurnOn Screen updating and Events

With Application

    .ScreenUpdating = True

    .EnableEvents = True

End With

End Sub

 4) 개발도구 > 매크로에서 실행

5) TOC 쉬트에 목차 만들어짐