#1
|
|||
|
|||
Browse to a specific sheet in an excel workbook
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? |
#2
|
|||
|
|||
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.
|
#3
|
||||
|
||||
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
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. |
#5
|
||||
|
||||
Quote:
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
navigate to excel sheets, open a specific sheet |
|
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 |