VBA Example – Create a directory sheet

DownloadcreateDirecotySheet

Function: Create a directory sheet whose cells is a hyperlink to the sheet

Image 007Image 008

 

Image 006

Source Code

Sub generateDirectorySheet()
directorySheetName = “Directory"

‘ No. of Worksheets
WS_Count = ActiveWorkbook.Worksheets.Count

Dim directorySheet As Worksheet
requiredToCreateDirectorySheet = True

Set sheetNames = CreateObject(“System.Collections.ArrayList")

For i = 1 To WS_Count
If Worksheets(i).Name = directorySheetName Then
Set directorySheet = Worksheets(i)
requiredToCreateDirectorySheet = False
Else
sheetNames.Add (Worksheets(i).Name)
End If
Next i

If requiredToCreateDirectorySheet = True Then
Set directorySheet = Worksheets.Add(Before:=Worksheets(1))
directorySheet.Name = directorySheetName
End If

For i = 0 To sheetNames.Count() – 1
directorySheet.Hyperlinks.Add anchor:=directorySheet.Cells(i + 1, 1), Address:="", SubAddress:=sheetNames(i) + “!A1″, TextToDisplay:=sheetNames(i)
Next i

End Sub

 

 

 

廣告

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s