Consulting

Results 1 to 4 of 4

Thread: Excel Table of Contents/Summary Help

  1. #1
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    1
    Location

    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.

    Capture.JPG
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,461
    Avoid merged cells in VBA & Excel.

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,967
    Location
    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
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,440
    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.
    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
  •