Results 1 to 4 of 4

Thread: Excel Table of Contents/Summary Help

  1. #1
    VBAX Newbie
    Apr 2019

    Excel Table of Contents/Summary Help

    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.

    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru
    Apr 2012
    Avoid merged cells in VBA & Excel.

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Apr 2005
    It looks to me that it already creates the ToC, so what are you asking for?
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Knowledge Base Approver VBAX Guru
    Oct 2005
    Surrey UK
    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
      .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
      .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.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts