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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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