Automatically generated Hyperlinks to internal worksheets which are also automatically generated

[Thrasher857]

So, my girlfriend is a teacher. Her school is FINALLY requiring them to track real-time data usage on her students. I'm not new to excel, I'm a scientist who tracks thousands of lines of data everyday, but VBA and macros aren't my specialty.

I have put together the module below, and would like help with the line commented out for automatically creating hyperlinks. Or any suggestions for a complete overhaul of this sub. Her student names are in a worksheet "Students" A2:A31.

____________________________________________________________

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Application.ScreenUpdating = False

Range("A2:G31").Select
Range("G31").Activate
ActiveWorkbook.Worksheets("Students").sort.SortFields.Clear
ActiveWorkbook.Worksheets("Students").sort.SortFields.Add Key:=Range("A2:A31" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Students").sort
.SetRange Range("A2:G31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set MyRange = Sheets("Students").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheets

'PLEASE HELP WITH THE LINE BELOW, I'D LIKE A HYPERLINK TO OCCUR BETWEEN THE STUDENTS NAME AND STUDENTS TAB

'ActiveSheets.Hyperlinks.Add Anchor:=MyCell, Address:=(="Sheets(Sheets.Count)"), SubAddress:=("Sheets(Sheets.Count)"), TextToDisplay:=MyCell.Value

Next MyCell

Worksheets("Students").Activate

Application.ScreenUpdating = True
End Sub

____________________________________________________________

I have come across mrexcel when I began learning VBA, and this seems like it is populated with very useful registrants. Please help me develop this tool not only for my gf, but an entire school district. She only has 6 students, but I'm building this file for up-to 30. (What they came up with was absolutely ridiculous, I laughed so hard I think I wet myself)

 

[sijpie]

You are screwing up with the double quotes and the required contents.

'this is what the end result should look like:
'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "MySheet!A1", TextToDisplay:="MySheet"
 
' so we need to make the code like this:
ActiveSheets.Hyperlinks.Add Anchor:=MyCell, Address:="", SubAddress:=Sheets(Sheets.Count).name & "!A1", TextToDisplay:=MyCell.Value

if you post code, then please put it between code tags (press the # button in the post edit screen or type [ code] [/ code] (without the spaces) before / behind your code

 

[Thrasher857]

Thank you SijPie for showing me how to post code in the future properly. I have added your code but unfortunately I'm receiving the Runtime Error '424' "Object Required" on the newly added line (In Red Below)

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
 
Application.ScreenUpdating = False
Range("A2:G31").Select
Range("G31").Activate
ActiveWorkbook.Worksheets("Students").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Students").Sort.SortFields.Add Key:=Range("A2:A31" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Students").Sort
.SetRange Range("A2:G31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set MyRange = Sheets("Students").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheets
[COLOR=lime]'SijPie [/COLOR][URL="http://www.MrExcel.com/forum"][COLOR=lime]www.MrExcel.com/forum[/COLOR][/URL][COLOR=lime]    09/10/2010[/COLOR]
[COLOR=lime]'this is what the end result should look like:[/COLOR]
[COLOR=lime]'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _[/COLOR]
[COLOR=lime]       "MySheet!A1", TextToDisplay:="MySheet"[/COLOR]
[COLOR=lime]' so we need to make the code like this:[/COLOR]
 
[B][COLOR=red]ActiveSheets.Hyperlinks.Add Anchor:=MyCell, Address:="", SubAddress:=Sheets(Sheets.Count).Name & "!A1", TextToDisplay:=MyCell.Value[/COLOR][/B]
 
Next MyCell
Worksheets("Students").Activate
Application.ScreenUpdating = True
End Sub

I have tried with an without the underscore, but that is being labeled as an invalid character. Again, fairly new to VBA, please be gentle.

 

[sijpie]

just for debugging add msgbox in front of the offending line.

That will show what the line looks like at runtime, and perhaps it will show that the sheets or so are named wrongly. The sheet names and range names should turn out as in your spreadsheet. Let me know.

msgbox ActiveSheets.Hyperlinks.Add Anchor:=MyCell, Address:="", _ 
        SubAddress:=Sheets(Sheets.Count).name & "!A1", TextToDisplay:=MyCell.Value

The single _ I have put at the end of the line is an indicator to VBA that the code continues (to make super long lines fit in your viewfield)

 

[Thrasher857]

Okay, I finally found it. Thank you SijPie for your help in the process. Below is the code that will take a list ("Students") from the workseet "Students" in A2:A31, generate a new worksheet for each student, and hyperlink their name from the "Students" worksheet to their individual worksheet.

I had to change ActiveSheets to ActiveCell.

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("Students").Sort.SortFields.Add Key:=Range("A2:A31" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Students").Sort
.SetRange Range("A2:G31")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set MyRange = Sheets("Students").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheets
'SijPie [URL="http://www.MrExcel.com/forum"]www.MrExcel.com/forum[/URL]    09/10/2010
'this is what the end result should look like:
'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "MySheet!A1", TextToDisplay:="MySheet"
 ' so we need to make the code like this:
 ActiveCell.Hyperlinks.Add Anchor:=MyCell, Address:="", SubAddress:=Sheets(Sheets.Count).Name & "!A1", TextToDisplay:=MyCell.Value
Next MyCell
Worksheets("Students").Activate
Application.ScreenUpdating = True
End Sub