VBA table of contents date as hyperlink formatting

[Jmoz092]

Hi, Im trying to create a Table Of Contents Page with hyperlinks to tabs in my workbook. The tabs are named as dates throughout the year in MM-DD-YY format. When I run the macro to create the TOC with hyperlinks, the tab names are displayed in M/DD/YY format. This, appears, to be causing the links to not anchor correctly. Is there a way I can alter the code to fix this problem?

Sub SheetNamesForTOCa()
 
Dim ws As Worksheet
Dim x As Integer




Application.ScreenUpdating = False


x = 2
For Each ws In Worksheets
If ws.Name Like "01-**-**" Then
     Sheets("Table Of Contents").Cells(x, 1) = ws.Name
     x = x + 1
End If
Next ws


Dim C As Range
With Sheets("Table of Contents")
    For Each C In .Range("A2:A32")
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With


With Sheets("Table Of Contents")
Range("A1").Value = ("January")
End With


Call SheetNamesForTOCb
Call SheetNamesForTOCc
Call SheetNamesForTOCd
Call SheetNamesForTOCe
Call SheetNamesForTOCf
Call SheetNamesForTOCg
Call SheetNamesForTOCh
Call SheetNamesForTOCi
Call SheetNamesForTOCj
Call SheetNamesForTOCk
Call SheetNamesForTOCl


Application.ScreenUpdating = True


End Sub

Also, How can I code it to where it only makes as many hyperlinks as there are tabs for each month?

 

[PatOBrien198]

Try using the "Format" function with the hyperlink subAddress.....

With Sheets("Table of Contents")
For Each C In .Range("A2:A32")
.Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & Format(C.Value, "mm-dd-yy") & "'!A1"
Next C
End With

 

[Jmoz092]

Thank you, that worked. I had the syntax wrong in applying the format function. Much appreciated