Making a Automatic Updatable Hyperlink List
Yes Hello Everyone and thanks for looking at my Thread I am currently trying to Develope a Small Database of Student and Would like After I make a New Worksheet Named After Each Student
Example
"Sarah Lee"
"Michael Jordan"
"Bill Cosby"
That on the Worsheet Named "Student List" which is in the same workbook Will Make a List of all the worksheets in the Current Workbook, Put them in Alphabetical Order and then Create a Hyperlink to Each Worksheet with the names on the page as hyperlinks and furthermore exclude the Student List Worksheet from the List ? and lets say to make it even better, start the list from Cell (F5) on the Student List Worksheet and the names go down from this Cell
Hey,
Sub Insert_TableOfContents()
Dim wBook As Workbook
Dim slSheet As Worksheet, wSheet As Worksheet
Dim iRov As Integer
Set wBook = ActiveWorkbook
Set slSheet = wBook.Worksheets("Student List")
ScreenUpdating = False
iRov = 5
For Each wSheet In wBook.Worksheets
If wSheet.Name <> slSheet.Name Then
slSheet.Hyperlinks.Add slSheet.Cells(iRov, 6), "", _
SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name
iRov = iRov + 1
End If
Next wSheet
ScreenUpdating = True
End Sub
Best Regards
Cathrine
I am getting an Error at the Red Highlighted region of this Code , And Since I am a beginner to VBA I dont know really how to trouble shoot this or what the error trualy is
It is a Complie Sytax Error if that helps at all
Sub Insert_TableOfContents()
Dim wBook As Workbook
Dim slSheet As Worksheet, wSheet As Worksheet
Dim iRov As Integer
Set wBook = ActiveWorkbook
Set slSheet = wBook.Worksheets("Student List")
ScreenUpdating = False
iRov = 5
For Each wSheet In wBook.Worksheets
If wSheet.Name <> slSheet.Name Then
slSheet.Hyperlinks.Add slSheet.Cells(iRov, 6), "", _
SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name
iRov = iRov + 1
End If
Next wSheet
ScreenUpdating = True
End Sub
Hey,
In your VBA Editor, if you have an empty line between these two lines, remove that line.
Cath
OK I got the code to work but All the Hyperlinks is Invalid i keep getting message " Reference not Valid" Am I doing something wrong ?
If the code you have now still looks like the one I posted, it should work. You have to check it out.
Cath
If you are using a non continuous worksheet name, like your example Sarah Lee, this will with my code return the message "Reference not Valid".
Remove these two lines:
slSheet.Hyperlinks.Add slSheet.Cells(iRov, 6), "", _
SubAddress:=wSheet.Name & "!A1", TextToDisplay:=wSheet.Name
and instead insert:
slSheet.Hyperlinks.Add slSheet.Cells(iRov, 6), "", _
SubAddress:="'" & wSheet.Name & "'!A1", TextToDisplay:=wSheet.Name
This will handle both, continuous and non continuous, worksheet names.
Sorry about this!
Cath
Hey,
You asked for: “Put them in Alphabetical Order and then Create a Hyperlink to Each Worksheet”, here you go…
Code Snippet
Sub SortWorksheetsAlphabetically_InsertTableOfContents()
Dim i As Integer, j As Integer, iRov As Integer, _
FirstWSToSort As Integer, LastWSToSort As Integer
Dim SortDescending As Boolean
Dim wBook As Workbook
Dim slSheet As Worksheet, wSheet As Worksheet
Set wBook = ActiveWorkbook
Set slSheet = wBook.Worksheets("Student List")
SortDescending = False
FirstWSToSort = 2
LastWSToSort = Worksheets.Count
ScreenUpdating = False
slSheet.Move Before:=Sheets(1)
For j = FirstWSToSort To LastWSToSort
For i = j To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(i).Name) > UCase(Worksheets(j).Name) Then
Worksheets(i).Move Before:=Worksheets(j)
End If
Else
If UCase(Worksheets(i).Name) < UCase(Worksheets(j).Name) Then
Worksheets(i).Move Before:=Worksheets(j)
End If
End If
Next i
Next j
iRov = 5
For Each wSheet In wBook.Worksheets
If wSheet.Name <> slSheet.Name Then
slSheet.Hyperlinks.Add slSheet.Cells(iRov, 6), "", _
SubAddress:="'" & wSheet.Name & "'!A1", TextToDisplay:=wSheet.Name
iRov = iRov + 1
End If
Next wSheet
ScreenUpdating = True
End Sub
Cath
