Excel sheets

ubns · #1 04-12-2012, 09:29 PM

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.

Catalin.B · #2 04-12-2012, 11:21 PM

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

ubns · #3 04-15-2012, 04:13 PM

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?

Catalin.B · #4 04-15-2012, 10:31 PM

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....

ubns · #5 04-15-2012, 10:48 PM

Thanks. Works perfectly.

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
Movement slide with excel sheets	majed	PowerPoint	4	01-17-2009 04:24 PM