Linking to sheets Macro

**Tmc2159** · 06-11-2013, 03:35 PM

I have an excel worsheet with a bunch of sheets with different names

I want a macro that creates a new sheet with links to all the other sheets, the link should be the name of the other sheets.

**mrice** · 06-11-2013, 03:41 PM

Try this macro when the sheet on which you want the hyperlinks to appear is the one which is active.

Sub Test()
Dim Sheet As Worksheet
For Each Sheet In Sheets
    If Sheet Is ActiveSheet = False Then
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(Rows.Count, 1).End(xlUp).Offset(1, 0), Address:="", SubAddress:= _
        Sheet.Name & "!A1", TextToDisplay:=Sheet.Name
    End If
Next Sheet
End Sub

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

**Tmc2159** · 06-11-2013, 03:54 PM

Thanks mrice

Only problem is that when I click on one of the links it tells me :

reference is not valid.

Note that i added a small bit to the code so that it would create a new sheet in the front.

Note that my first sheet was calledstage1

Sub AddingAsheet()
'
' AddingAsheet Macro
'

'
    Sheets("Stage1").Select
    Sheets.Add
    
    Dim Sheet As Worksheet
For Each Sheet In Sheets
    If Sheet Is ActiveSheet = False Then
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(Rows.Count, 1).End(xlUp).Offset(1, 0), Address:="", SubAddress:= _
        Sheet.Name & "!A1", TextToDisplay:=Sheet.Name
    End If
Next Sheet


End Sub

**mrice** · 06-12-2013, 03:24 AM

Just to mention that

ActiveSheet.Hyperlinks.Add Anchor:=Cells(Rows.Count, 1).End(xlUp).Offset(1, 0), Address:="", SubAddress:= _
"'" & Sheet.Name & "'!A1", TextToDisplay:=Sheet.Name

is more robust in the case where there are spaces in the names of the sheets.