PDA

View Full Version : Solved: Basic TOC



J.Winnfield
10-20-2011, 03:26 PM
Hi everyone, I've been searching for a while and cannot find this simple macro. I want to create a table of contents that pulls the value in cell B2 of every worksheet and compiles it in cell B2 and down. I also want each value hyperlinked to cell A1 of the respective worksheet.

Finally, I need to edit the Format of row A and row B (font, font size, shading, font color) for each worksheet.

Any help is greatly appreciated.

Thanks,

Simon Lloyd
10-21-2011, 12:23 AM
This will create a hyperlinked list of all the "B2"'s except sheet1, it works on the active sheet so only run the code on the sheet for the intended results or adjust the code to specify a sheet.Sub quick_links()
Dim sh As Worksheet, i As Long
For i = 2 To Sheets.Count
ActiveSheet.Hyperlinks.Add Anchor:=Range("B" & Rows.Count).End(xlUp).Offset(1, 0), Address:="", _
SubAddress:=Sheets(i).Range("B2").Value, TextToDisplay:=Sheets(i).Range("B2").Value
Next i
End Sub

J.Winnfield
10-21-2011, 05:58 AM
This will create a hyperlinked list of all the "B2"'s except sheet1, it works on the active sheet so only run the code on the sheet for the intended results or adjust the code to specify a sheet.Sub quick_links()
Dim sh As Worksheet, i As Long
For i = 2 To Sheets.Count
ActiveSheet.Hyperlinks.Add Anchor:=Range("B" & Rows.Count).End(xlUp).Offset(1, 0), Address:="", _
SubAddress:=Sheets(i).Range("B2").Value, TextToDisplay:=Sheets(i).Range("B2").Value
Next i
End Sub

Thank you sir. This is a great start. The hyperlinks are still not working but I am sure I can figure it out.

Cheers,

J.Winnfield
10-21-2011, 06:14 AM
Thank you sir. This is a great start. The hyperlinks are still not working but I am sure I can figure it out.

Cheers,

Nevermind, I figured it out and is now working. Thanks.

Simon Lloyd
10-21-2011, 07:11 AM
If this is solved please take the time to mark it by going to the top of your first post, right hand side, Thread Tools, Mark Solved! :)