Printable View
I am extremely new to VBA and am actually teaching myself. I have a workbook that I would like to have a table of contents for. I need to have information from 2 static cells used to create the table of contents and would like the name to be hyperlinked to each sheet. I have attached the workbook I have so far. I need to pull the highlighted fields for the table of contents.
Attachment 24115
Avoid merged cells in VBA & Excel.
It looks to me that it already creates the ToC, so what are you asking for?
Try:It no longer matters what the sheets are called since it uses the name in cell B4.Code:Sub blah()
Dim Destn As Range, ShtNo As Long, Sht As Worksheet, TTDisplay
'Assumption that active sheet is always the Contents sheeet (it doesn't have to be called 'Contents'.
With ActiveSheet
.UsedRange.Clear
.Range("A1") = "Table of Contents"
.Range("A1").Font.Bold = True
.Columns("A").ColumnWidth = 3.86
.Range("A1").Font.Size = 16
.Range("A1:C1").Borders(xlEdgeBottom).Weight = xlThin
Set Destn = .Range("B3")
ShtNo = 0
For Each Sht In ThisWorkbook.Worksheets
If Sht.Range("A1").Value = "VTH Rabies Vaccination Record" And Sht.Range("A5").Value = "CWID" Then 'it's definitely a record sheet.
ShtNo = ShtNo + 1
If Application.Trim(Sht.Range("B4").Value) = "" Then TTDisplay = "Blank" Else TTDisplay = Application.Trim(Sht.Range("B4").Value)
.Hyperlinks.Add Destn, "", SubAddress:="'" & Sht.Name & "'!A1", TextToDisplay:=TTDisplay
Destn.Offset(, -1).Value = ShtNo
With Destn.Offset(, 1)
.Value = IIf(Left(Sht.Range("E8").Value, 6) = "Action", "no next action date", Sht.Range("E8").Value)
.NumberFormat = "m/d/yyyy" 'or whatever format you want
End With 'Destn.Offset(, 1)
Set Destn = Destn.Offset(1)
End If
Next Sht
.Columns(2).EntireColumn.AutoFit
.Range(.Cells(3, "B"), Destn.Offset(-1, 1)).Sort key1:=.Cells(2, "B"), order1:=xlAscending, Header:=xlNo, Orientation:=1
.Range("A:Z").Font.Name = "Cambria"
End With 'ActiveSheet
End Sub