Create a table of contents with page numbers

[StuLux]

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).

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?

 

[StuLux]

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/table-of-contents-macro.php

 

[lala_land]

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?

 

[lala_land]

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?