Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Table of Contents with Page Numbers
G'day all
I have been using Don Guillett's excellent code (copied below) to create a automatic Table of Contents. Is there any way to automatically include page numbers to the right of the list of sheet names (obviously in its own cells)? Also I have information which is to be manually entered against the list on the left of the list of sheet names, so any code would have to take into account this as well. Also I took out the sort function as I don't need it. FYI - Don's code starts on A1 of the sheet. I wanted it to start on a different cell so I made the following changes to make the list start at C3. Cells(i, 1).Value = ms to Cells(3 + i, 3).Value = ms If Target.Column < 1 Then Exit Sub to If Target.Column < 3 Then Exit Sub Thanks to all. Don Guillett's Code Private Sub Worksheet_Activate() Dim i As Long Dim ms As String For i = 1 To Sheets.Count ms = Sheets(i).Name 'MsgBox ms If ms < ActiveSheet.Name Then Cells(i, 1).Value = ms End If Next i 'sorts list Columns("a").sort key1:=Cells(2, 1) End Sub '======= Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) If Target.Column < 1 Then Exit Sub Application.DisplayAlerts = False Dim WantedSheet As String WantedSheet = Trim(ActiveCell.Value) If WantedSheet = "" Then Exit Sub On Error Resume Next If Sheets(WantedSheet) Is Nothing Then 'GetWorkbook ' calls another macro to do that Else Application.GoTo Sheets(WantedSheet).Range("a4") End If Application.DisplayAlerts = True End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table of Contents with Page Numbers | Excel Discussion (Misc queries) | |||
Contents Page | Excel Discussion (Misc queries) | |||
merging documents,exporting long landscape table to word with page breaks & numbers | Excel Discussion (Misc queries) | |||
start page numbering after table of contents | Excel Discussion (Misc queries) | |||
Automatically print all page contents in pivot table. | Excel Worksheet Functions |