too many sheet i want shortcut of all my sheets on the first sheet,possible?

mark692

Active Member
Joined
Feb 27, 2015
Messages
321
Office Version
  1. 2016
Platform
  1. Windows
i have many sheet and i want to create my first sheet to summarize all my sheets, and when i click the sheet name on my first sheet it will bring me to the sheet that i clicked is that possible?thanks :)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
go search out ASAP utilities, free for home use, though I bought a copy as its worth supporting

It has a routine to index all your sheets and provide hyperlinks
 
Upvote 0
with the sheetnames in column B,

put this in column A and copy down


Excel 2012
AB
1IndexDescriptions
2Name1Name1
3Name2Name2
4Name3Name3
5Name4Name4
6Name5Name5
7Name6Name6
8Name7Name7
9Name8Name8
10Name9Name9
11Name10Name10
Index
Cell Formulas
RangeFormula
A2=HYPERLINK("#"&B2&"!"&"A1",B2)
 
Last edited:
Upvote 0
Not knowing where your sheet names are you can use the below script to put all your sheet names in column "A" of Sheet (1) beginning with row (2)
Code:
Sub Sheet_Names_Me()
Dim i As Integer
Sheets(1).Activate
For i = 2 To Sheets.Count
Cells(i, 1).Value = Sheets(i).Name
Next
End Sub



Then put this Sheet Event script into Sheet(1)
Then when you double click on the sheet name in column ("A") of Sheet(1)
You will be taken to that sheet.
To install this code:

Right-click on Sheet (1) tab
Select View Code from the pop-up context menu
Paste the below code in the VBA edit window

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Cancel = True
On Error GoTo M
Sheets(Target.Value).Activate
End If
Exit Sub
M:
MsgBox "No such sheet exist"
End Sub
 
Upvote 0
Or if you prefer Hyperlinks to each sheet in your workbook.
Run this script and it will put a hyperlink in column "A" of sheet(1) for every sheet in your workbook.

Code:
Sub AddHyperLinks()
Dim c As Range
Dim i As Integer
Sheets(1).Activate
For i = 2 To Sheets.Count
Cells(i, 1).Value = Sheets(i).Name
Next

With Sheets(1)
    For Each c In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=c, Address:="", SubAddress:="'" & c.Value & "'!A1"
    Next c
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,212,046
Messages
6,105,590
Members
447,972
Latest member
carrieann

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top