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

mark692

Active Member
Joined
Feb 27, 2015
Messages
316
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,714
Office Version
  1. 2013
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,714
Office Version
  1. 2013
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,675
Messages
5,838,718
Members
430,566
Latest member
ChanchalSingh

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
Top