Results 1 to 4 of 4

Create a table of contents with page numbers

This is a discussion on Create a table of contents with page numbers within the Excel Questions forums, part of the Question Forums category; I have code that will create a table of contents for an Excel workbook i.e. create a list of all ...

  1. #1
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    578

    Default Create a table of contents with page numbers

    I have code that will create a table of contents for an Excel workbook i.e. create a list of all the tabs on a new worksheet with hyperlinks to the tabs (see code below).

    Code:
    Sub IndexWorksheets()
    Dim ws As Worksheet
    Dim Location As Range 'Place where list is to be made
    Dim WSCount As Integer ' Count of worksheets in book
    Dim i As Integer
    'request location for list
    Set Location = Application.InputBox(Prompt:="Where do you want to begin your index:", Type:=8)
    WSCount = Worksheets.Count
    For i = 1 To WSCount 'for each worksheet in book
        'sets hyperlink to sheet
        With Location
            .Value = Sheets(i).Name
            .Hyperlinks.Add Anchor:=Location, Address:="", _
            SubAddress:="'" & Sheets(i).Name & "'!A1"
        End With
     
      Next i
    End Sub
    I have a workbook that has several tabs that is used to create a pack of information, I would like to be able to auto-generate the table of contents before publishing but also include, alongside the tab name, the sheet number(s) relating to that tab (as part of a group of tabs). The numbers of sheets on each tab could vary and it is also possible to have extra tabs added/deleted from month to month. I have a footer that shows page x of xx which works correctly when the sheets are grouped and printed so I suppose what I need is code that finds these same values - has anybody attempted/achieved this or can give me a pointer as to where I might start?
    Stuart
    "It's 5 o'clock somewhere"

    XP Pro (SP 3)/Excel 2007 (work) and Windows 8/Excel 2013 (home)

  2. #2
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    578

    Default Re: Create a table of contents with page numbers

    After some further seraching I found this - looks like a solution, I will test and see. Thanks to those of you who did view my question.

    http://www.mrexcel.com/articles/tabl...ents-macro.php
    Stuart
    "It's 5 o'clock somewhere"

    XP Pro (SP 3)/Excel 2007 (work) and Windows 8/Excel 2013 (home)

  3. #3
    New Member
    Join Date
    Jul 2010
    Posts
    6

    Default Re: Create a table of contents with page numbers

    I would like to link all of my worksheets to a Master List in the same workbook.
    So I created a worksheet named Master List and have a column with the heading of File Ref.
    It looks like this:
    File Ref.
    WC 1/10 - I would like to link this to worksheet "c1"
    WC 2/10 - link to worksheet "c2"
    WC 3/10 - link to worksheet "c3"...................... and so on.

    Hyperlink each of them one by one would take a long time and I don't really understand the codes.

    Will I be able to auto hyperlink all those worksheets to the Master List by coping the codes from the URL provided by making some name changes?

  4. #4
    New Member
    Join Date
    Jul 2010
    Posts
    6

    Default Re: Create a table of contents with page numbers

    Hmm... Ok, I tried using macro but I hope to get some help on the vba code.

    Here's the code:
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 26/07/2010 by localadmin
    '
    ' Keyboard Shortcut: Ctrl+Shift+H
    '
    Range("A6").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "'c5'!A1", TextToDisplay:="WC 05/10"
    End Sub

    The last part of the code: "'c5'!A1", TextToDisplay:="WC 05/10"
    like for the next worksheet c6, the text to display will be WC06/10.

    Since this code will only work solely for worksheet c5, how can I make it work for other worksheets in the same workbook as well?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com