Excel Table of Contents/Summary Help

[jamessd]

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.

Capture.JPG

[snb]

Avoid merged cells in VBA & Excel.

[Bob Phillips]

It looks to me that it already creates the ToC, so what are you asking for?

[p45cal]

Try:

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

It no longer matters what the sheets are called since it uses the name in cell B4.