StuLux
Well-known Member
- Joined
- Sep 14, 2005
- Messages
- 679
- Office Version
- 365
- Platform
- Windows
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).
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?
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?