Excel Macro - Make Excel Sheet List

make-excel-sheet-list.xlsm

Sub MakeSheetList_Click()
Dim Ws As Worksheet
  Dim ix As Integer, it As Integer
  Set Ws = Sheets("시트목록")

  With Ws
     it = Worksheets.Count
     .Columns("A:B").EntireColumn.ClearContents
     .Range("a1:b1") = Array("번호", "시트명")

     For ix = 1 To it
        .Cells(ix + 1, 1) = ix
        .Cells(ix + 1, 2) = Sheets(ix).Name
    Next ix

    .Columns("A:B").EntireColumn.AutoFit

  End With
End Sub

Sub AutoSheet_Open()
For Each c In Range("C3:C23")
If IsExSheet(c.Value) = False Then
    Set s = Worksheets.Add
    s.Name = c.Value
End If
Next
End Sub

Function IsExSheet(nam) As Boolean
For Each s In Sheets
    If nam = s.Name Or nam = "" Then
        IsExSheet = True
        Exit Function
    End If
Next
End Function

Sub AutoLink_Open()

Dim cnt_ev As Integer
Dim wb As Workbook
cnt_ev = Application.ActiveWorkbook.Sheets.Count   '활성화된 워크북의 모든 시트 갯수 반환
Set wb = Workbooks(Application.ActiveWorkbook.Name)   '파일 set
Worksheets("목록").Select   '첫번째 시트에 내용이 기록되게 Sheet1 선택
'Range("a1").Value = "Sheet List (총 " & cnt_ev & " 시트 링크)"   '전체 시트의 수 기록

For i = 1 To cnt_ev
    Range("i" & i + 2).Select   '시트이름이 출력될 셀 선택
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & wb.Sheets(i).Name & "'!A1", TextToDisplay:=wb.Sheets(i).Name '시트이름과 링크
Next

End Sub