Browse to a specific sheet in an excel workbook

macropod · #4 04-08-2014, 05:53 PM

Quote:

Originally Posted by Pecoflyer

You can also create a sheet index as explained here.
This macro creates it in the same workbook, but if you Google around a bit I'm sure somebody has the second part of the answer

For the second part, simply use the following cut-down version of the macro, then copy the resulting index from column B to the other workbook:

Code:

Sub MakeLinks()
Dim wSheet As Worksheet, l As Long
l = 1
With Me
  .Columns(2).ClearContents
  .Cells(1, 2) = "INDEX"
  .Cells(1, 2).Name = "Index"
End With
For Each wSheet In Worksheets
  If wSheet.Name <> Me.Name Then
    l = l + 1
    Me.Hyperlinks.Add Anchor:=Me.Cells(l, 2), Address:=ThisWorkbook.FullName, _
    SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
  End If
Next wSheet
End Sub

Note the use of 'ThisWorkbook.FullName' to add what will be the destination workbook's name and that the 'Back to Index' hyperlink created via the macro in the link won't take you back to the originating workbook in this case. Personally, I'd rather add the forward & back buttons to the QAT than mess around putting a 'Back to Index' hyperlink on every worksheet.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Exporting workbook specific sheet columns on exit	shabbaranks	Excel Programming	4	08-21-2012 08:00 AM
permanently unprotecting a sheet or workbook	mrmagoo	Excel	2	07-04-2012 08:12 PM
Shared Excel workbook	eguru2	Excel	0	07-12-2011 12:02 AM
paste graph between workbook/sheet	sandsoppa	Excel	2	02-25-2011 02:29 PM
How do I merge data from one sheet in a workbook out into multiple sheets	nolesca	Excel	4	06-07-2010 08:13 AM