Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2014, 05:30 AM
Sinister Sinister is offline Browse to a specific sheet in an excel workbook Windows 7 64bit Browse to a specific sheet in an excel workbook Office 2010 32bit
Novice
Browse to a specific sheet in an excel workbook
 
Join Date: Apr 2014
Posts: 1
Sinister is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 04-07-2014, 03:47 PM
gebobs gebobs is offline Browse to a specific sheet in an excel workbook Windows 7 64bit Browse to a specific sheet in an excel workbook Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 834
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 04-07-2014, 11:54 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Browse to a specific sheet in an excel workbook Windows 7 64bit Browse to a specific sheet in an excel workbook Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,432
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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
Reply With Quote
  #4  
Old 04-08-2014, 05:41 AM
gebobs gebobs is offline Browse to a specific sheet in an excel workbook Windows 7 64bit Browse to a specific sheet in an excel workbook Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 834
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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.
Reply With Quote
  #5  
Old 04-08-2014, 05:53 PM
macropod's Avatar
macropod macropod is offline Browse to a specific sheet in an excel workbook Windows 7 32bit Browse to a specific sheet in an excel workbook Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,496
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by Pecoflyer View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
navigate to excel sheets, open a specific sheet

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Browse to a specific sheet in an excel workbook Exporting workbook specific sheet columns on exit shabbaranks Excel Programming 4 08-21-2012 08:00 AM
Browse to a specific sheet in an excel workbook 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
Browse to a specific sheet in an excel workbook How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 02:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft