Browse to a specific sheet in an excel workbook

Sinister · #1 04-07-2014, 05:30 AM

I have information on separate states of USA in separate sheets of an excel workbook named 'A.xlsx'. The name of each sheet is a unique state name.

I have another excel workbook - 'B.xlsx' where I have a table and each row of the table needs to have a url to one of the sheets of workbook A. If I want to know the information about California I click on its specific URL which should take me to its specific sheet in the workbook A. Similarly, if I want to fetch information on Kansas I should be taken to the sheet named Kansas in workbook A.

How can I achieve this?

gebobs · #2 04-07-2014, 03:47 PM

Insert a hyperlink. In the Hyperlink dialog, select the file you want to use and then press the Hyperlinks button which should display your tabs.

Pecoflyer · #3 04-07-2014, 11:54 PM

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

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.

gebobs · #5 04-08-2014, 05:41 AM

Ah...that's nice. But it is dynamic. As far as I can tell, the worksheets here are never going to change until the US adds or deletes a state. Thus, once an index is generated with this, the macro can be deleted and the sheet saved once again as a normal spreadsheet.

I have to admit, my Excel VB is not all that great, but if someone could figure out how to have this populate another worksheet, that would save a lot of tedious hyperlinking.

Best of all would be to have it run non-native of either worksheet so that they don't have to be saved as macro-enabled.

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