PDA

View Full Version : Excel Table of Contents/Summary Help



jamessd
04-22-2019, 01:00 PM
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.

24115

snb
04-23-2019, 02:32 AM
Avoid merged cells in VBA & Excel.

Bob Phillips
04-23-2019, 10:15 AM
It looks to me that it already creates the ToC, so what are you asking for?

p45cal
04-23-2019, 10:53 AM
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.