Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-12-2012, 09:29 PM
ubns ubns is offline Excel sheets Windows 7 32bit Excel sheets Office 2010 32bit
Competent Performer
Excel sheets
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default Excel sheets

Hi



I have lot of sheets in the excel. on the first page I want to include the name of all those worksheets along with hyperlinks. Any way.
Reply With Quote
  #2  
Old 04-12-2012, 11:21 PM
Catalin.B Catalin.B is offline Excel sheets Windows Vista Excel sheets Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

You can try this code (if you don't want to add hiperlinks manually):
Code:
Sub shNames()
Dim Wks
For Each Wks In ThisWorkbook.Worksheets

              Range("A65536").End(xlUp).Offset(1, 0).Select

              ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
              SubAddress:=Wks.Name & "!A1", TextToDisplay:=Wks.Name

       Next Wks

End Sub
Reply With Quote
  #3  
Old 04-15-2012, 04:13 PM
ubns ubns is offline Excel sheets Windows 7 32bit Excel sheets Office 2010 32bit
Competent Performer
Excel sheets
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default

Hi Thanks for this, it does the job, however on clicking some of the hyperlinks for the worksheets (created by this macro) gives an error - "reference not valid"

Any suggestions?
Reply With Quote
  #4  
Old 04-15-2012, 10:31 PM
Catalin.B Catalin.B is offline Excel sheets Windows Vista Excel sheets Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Obviously, you have used spaces in sheet names. You do not give details, but i guess you have multiple words sheet names.
Change this part of the code:
SubAddress:= Wks.Name with:
SubAddress:= "'" & Wks.Name & "'"
to handle this error....
Reply With Quote
  #5  
Old 04-15-2012, 10:48 PM
ubns ubns is offline Excel sheets Windows 7 32bit Excel sheets Office 2010 32bit
Competent Performer
Excel sheets
 
Join Date: Apr 2012
Posts: 177
ubns is on a distinguished road
Default

Thanks. Works perfectly.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking sheets to fetch transactions from sheets to another waqer Excel 4 09-01-2011 12:35 PM
merge excel sheets Mahen Excel 2 08-03-2011 12:21 AM
Excel 2007 doesn't print color graphics on multiple sheets mwoolf Excel 1 06-19-2009 02:34 AM
unwanted sheets on opening excel msofficeman Excel 3 02-24-2009 03:13 PM
Excel sheets Movement slide with excel sheets majed PowerPoint 4 01-17-2009 04:24 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:16 AM.


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