Syntax ?

[davidlants]

Hi,

I'm trying to create a macro that runs through all my worksheets and creates a hyperlink to all of them in the INDEX worksheet with the name of the worksheet as the link, but I can't seem to figure out the hyperlinks line in the loop to do this correctly. Here's what I have so far:

(B4 in each worksheet has a description of the sheet AND THANKS FOR ANY HELP)

Sub name()

         Dim WS_Count As Integer
         Dim I As Integer
         Dim num As Long

         WS_Count = ActiveWorkbook.Worksheets.Count

         num = 1
         
         For I = 1 To WS_Count

            Sheets("INDEX").Range(Cells(num, 2)).Hyperlinks.Add Range(ActiveWorkbook.Worksheets(I).Value), Range(ActiveWorkbook.Worksheets(I).Value)
            Sheets("INDEX").Cells(num, 3).Value = Sheets(I).Range("B4").Value

            
            
                
            num = num + 1

         Next I

      End Sub

 

[Smitty]

Welcome to the Board!

Here's is a hyperlinked Table of Contents creator by Zack Barresse.

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> CreateTOC()<br>    <SPAN style="color:#007F00">'   Code by Zack Baresse</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> ActiveWorkbook <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book"<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>    <br>        <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet, _<br>            ct <SPAN style="color:#00007F">As</SPAN> Chart, _<br>            shtName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, _<br>            nrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>            tmpCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>            i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>            numCharts <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>        <br>        nrow = 3<br>        i = 1<br>        numCharts = ActiveWorkbook.Charts.Count<br>        <br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> hasSheet<br>        Sheets("Table of Contents").Activate<br>        <SPAN style="color:#00007F">If</SPAN> MsgBox("You already have a Table of Contents page.  Would you like to overwrite it?", _<br>        vbYesNo + vbQuestion, "Replace TOC page?") = vbYes <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> createNew<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br>hasSheet:<br>    Sheets.Add Before:=Sheets(1)<br>    <SPAN style="color:#00007F">GoTo</SPAN> hasNew<br><br>createNew:<br>    Sheets("Table of Contents").Delete<br>    <SPAN style="color:#00007F">GoTo</SPAN> hasSheet<br><br>hasNew:<br>    tmpCount = ActiveWorkbook.Charts.Count<br>    <SPAN style="color:#00007F">If</SPAN> tmpCount > 0 <SPAN style="color:#00007F">Then</SPAN> tmpCount = 1<br>        ActiveSheet.Name = "Table of Contents"<br>        <br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>            <SPAN style="color:#007F00">'.Cells.Interior.Color<SPAN style="color:#00007F">In</SPAN>dex = 4</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> .Range("B2")<br>                    .Value = "Table of Contents"<br>                    .Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>                    .Font.Name = "Calibri"<br>                    .Font.Size = "24"<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ActiveWorkbook.Worksheets<br>            nrow = nrow + 1<br>            <SPAN style="color:#00007F">With</SPAN> ws<br>                shtName = ws.Name<br>                <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>                    .Range("B" & nrow).Value = nrow - 3<br>                    .Range("C" & nrow).Hyperlinks.Add _<br>                        Anchor:=Sheets("Table of Contents").Range("C" & nrow), Address:="#'" & _<br>                        shtName & "'!A1", TextToDisplay:=shtName<br>                    .Range("C" & nrow).HorizontalAlignment = xlLeft<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> ws<br>        <br>        <SPAN style="color:#00007F">If</SPAN> numCharts <> 0 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ct In ActiveWorkbook.Charts<br>                nrow = nrow + 1<br>                shtName = ct.Name<br>                <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>                    .Range("B" & nrow).Value = nrow - 3<br>                    .Range("C" & nrow).Value = shtName<br>                    .Range("C" & nrow).HorizontalAlignment = xlLeft<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> ct<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <br>        <SPAN style="color:#00007F">With</SPAN> Sheets("Table of Contents")<br>            <SPAN style="color:#00007F">With</SPAN> .Range("B2:G2")<br>                .MergeCells = <SPAN style="color:#00007F">True</SPAN><br>                .HorizontalAlignment = xlLeft<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br>            <SPAN style="color:#00007F">With</SPAN> .Range("C:C")<br>                .EntireColumn.AutoFit<br>                .Activate<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .Range("B4").Select<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>        .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    MsgBox "Done!" & vbNewLine & vbNewLine & "Please note: " & _<br>        "Charts are listed after regular " & vbCrLf & _<br>        "worksheets and will not have hyperlinks.", vbInformation, "Complete!"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[davidlants]

This is awesome, thank you very much!!!