Table of content

[CNN]

I tried using one of the knowledge base articles vbaexpress.com/kb/getarticle.php?kb_id=16 to create a table of content. It worked fine except for one problem, the TOC always left out the last sheet in the workbook.
I went through the code and couldn't find anything limiting the number of sheets. However, I am very new at using VBA so I was hoping someone could tell me what was wrong.

Thanks in advance.

[SamT]

Is the last sheet a chart sheet? That code won't do Chart Sheets.

This is the code for the index sheet I use. I can't always find a common empty cell to put a hyperlink back to the index, So this code keeps the index Sheet next to the sheet selected from it's list.

[VBA]Option Explicit

Private Sub Worksheet_Activate()
'An interactive index that refreshes each time
'it's viewed so that it is always current, even if
'Sheets are added, deleted, or renamed.
'
'The Selection Change Sub below keeps this sheet next to
'the Sheet chosen when the User clicks on a Sheet name
'in the Sheet Index List.


'ShtNdx is the CodeName of "Sheet Index"
Const TopRowOfList As Long = 4 'Set As Desired
Dim i As Long 'Common index variable
Dim r As Long 'Row Counter for Index List
r = TopRowOfList 'Set here because it's used to clear the list

Application.ScreenUpdating = False

''''Clear the existing list
If LastRow >= TopRowOfList Then ShtNdx.Range("A" & CStr(TopRowOfList) & _
":A" & CStr(LastRow)).ClearContents

''''Create the List of Sheet Names
With ThisWorkbook
For i = 1 To .Sheets.Count
If .Sheets(i) Is ShtNdx Then
'Don't list This sheet
'Don't increment Row counter
GoTo NextLoop
Else
ShtNdx.Cells(r, 1) = .Sheets(i).Name
r = r + 1 'Increment Row Counter
End If
NextLoop:
Next i
End With

''''Sort the list alphabetically
Range("A4:A" & CStr(LastRow)).Sort _
Key1:=Range("A1"), _
Header:=xlNo

Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False

''''Check criteria for running this sub
'Is only one Cell selected
If Target.Count <> 1 Then Exit Sub
'Is there a List
If LastRow < 4 Then Exit Sub 'TopRowOfList
'Is the selection in the List
If Intersect(Target, Range("A4:A" & CStr(LastRow))) Is Nothing Then Exit Sub


''''Move the Index Sheet, Activate the chosen Sheet, and Select "A1" _
on the chosen Sheet.
ShtNdx.Move Before:=Sheets(Target.Value)
Sheets(Target.Value).Activate
ActiveSheet.Range("A1").Select

Application.ScreenUpdating = True
End Sub

Private Function LastRow() As Long
'Custom for this module.
'Always looks for the last non-empty cell in Column "A."
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
End Function[/VBA]

[SamT]

On reviewing that code, I realized that I had only designed it for my own use.

YOu must make sure that whatever Tab name you give the sheet, it's CodeName must be ShtNdx.

To make it Universal Move this line to above the first sub.
[VBA]Const TopRowOfList As Long = 4 'Set As Desired[/VBA]

Recode the "4" in these lines to use TopRowOfList
[VBA]If LastRow < 4 Then Exit Sub 'TopRowOfList
'Is the selection in the List
If Intersect(Target, Range("A4:A" & CStr(LastRow))) Is Nothing Then Exit Sub[/VBA]

Or, If you don't mind it being having hardcoded values and you really don't want the list starting at Row 4, just change all the values "4" to whichever row you want.

[CNN]

The last sheet is not a chart sheet. All the sheets contain the same format and formulas, just the input changes. I found another code to do what I wanted. I was just wondering why the first code kept skipping the last sheet.

[mancubus]

welcome to the forum.

the procedure in the KB article ( http://www.vbaexpress.com/kb/getarticle.php?kb_id=16 ) assumes active worksheet is the "Index" worksheet and active cell is the starting cell for hyperlinks to other worksheets.

if you are active in a worksheet other than "Index", this worksheet will be excluded from list.

related lines of the procedure:
[vba]Set WsInd = ActiveSheet[/vba]

[vba]If Ws.Name <> WsInd.Name Then[/vba]