Index pages

[James M]

Hi

Is there a way to create an index page which shows a link to every other page in a workbook? I know how to manually hyperlink them, but this is too labour intensive because of the volume of workbooks and the number of sheets in each.
Any suggestions gratefully recieved,

Many thanks.

 

[Ruddles]

Creat a worksheet called "Index", paste this into the worksheet code module and run it:-

Option Explicit
 
Public Sub IndexSheets()
 
  Dim ws As Worksheet
  Dim iRow As Long
 
  [COLOR=green]' clear old index entries[/COLOR]
  With Sheets("Index")
    .Range("A2:A" & CStr(.Rows.Count)).ClearContents
  End With
 
  iRow = 1
  For Each ws In Worksheets
    If ws.Name <> "Index" Then
      iRow = iRow + 1
      With Sheets("Index")
        .Hyperlinks.Add Anchor:=.Cells(iRow, 1), Address:="", _
            SubAddress:="'" & ws.Name & "'!A1", _
            TextToDisplay:="Go to " & ws.Name
      End With
    End If
  Next ws
 
End Sub

 

[James M]

Ruddles

This works brilliantly. Thanks so much for the assistance. It's going to save me so much time!!

Thanks again,

James

 

[Ruddles]

Obviously you cam modify the coding to suit, like the text which appears on the page or the column it appears in:-

Option Explicit
 
Public Sub IndexSheets()
 
  Dim ws As Worksheet
  Dim iRow As Long
 
  ' clear old index entries
  With Sheets("Index")
    .Range("A2:B" & CStr(.Rows.Count)).ClearContents
  End With
 
  iRow = 1
  For Each ws In Worksheets
    If ws.Name <> "Index" Then
      iRow = iRow + 1
      With Sheets("Index")
        .Cells(iRow, 1) = "To jump to sheet " & ws.Name & ":-"
        .Hyperlinks.Add Anchor:=.Cells(iRow, 2), Address:="", _
            SubAddress:="'" & ws.Name & "'![COLOR=blue][B]A1[/B][/COLOR]", _
            TextToDisplay:="Click here!"
      End With
    End If
  Next ws
 
End Sub

(The bit in blue specifies which cell you jump to in the target sheet, so this can also be changed if required.)

 

[James M]

Excellent. Adding this to my existing Macros has more or less fully automated the process I am working on. This means I can create and fully populate a new databook of something like 300-400 pages in about 10mins compared to the average time of around 5-6 hours to do the same task manually.
Thanks again for the help. I'm sure I'll be using this forum frequently in the future.

All the best

James

 

[Ruddles]

You didn't give your postal address. Where do I send the invoice?