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

mark692

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

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

mole999

Moderator
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,239
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

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Index</td><td style=";">Descriptions</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-decoration: underline;color: #0563C1;;">Name1</td><td style=";">Name1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-decoration: underline;color: #0563C1;;">Name2</td><td style=";">Name2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-decoration: underline;color: #0563C1;;">Name3</td><td style=";">Name3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-decoration: underline;color: #0563C1;;">Name4</td><td style=";">Name4</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-decoration: underline;color: #0563C1;;">Name5</td><td style=";">Name5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-decoration: underline;color: #0563C1;;">Name6</td><td style=";">Name6</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-decoration: underline;color: #0563C1;;">Name7</td><td style=";">Name7</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-decoration: underline;color: #0563C1;;">Name8</td><td style=";">Name8</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-decoration: underline;color: #0563C1;;">Name9</td><td style=";">Name9</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-decoration: underline;color: #0563C1;;">Name10</td><td style=";">Name10</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Index</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A2</th><td style="text-align:left">=HYPERLINK(<font color="Blue">"#"&B2&"!"&"A1",B2</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,266
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
17,266
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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