Create a table of contents with page numbers

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Create a table of contents with page numbers

  1. #1
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?

User Tag List

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