Create links to all sheets in a workbook programmatically
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly navigate to a particular worksheet, this is more useful if you have plenty of worksheets in your workbook.
Table of Contents
1. Create links to all sheets in a workbook
The macro inserts hyperlinks to all worksheets in your workbook except the current worksheet.
1.1 Video
Watch a video where I explain how to use the macro
1.2 VBA code
Sub CreateLinksToAllSheets()
Dim sh As Worksheet
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
If ActiveSheet.Name <> sh.Name Then
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
ActiveCell.Offset(1, 0).Select
End If
Next sh
End Sub
1.3 Explaining vba code
Sub CreateLinksToAllSheets()
All macros start with Sub and end with End Sub.
Dim sh As Worksheet
This line declares sh as a worksheet object.
Dim cell As Range
Cell is declared a range object. A range object can contain a single cell or multiple cells.
For Each sh In ActiveWorkbook.Worksheets
Each worksheet in active workbook is stored in sh, one by one.
If ActiveSheet.Name <> sh.Name Then
This If ... then line avoids linking to active worksheet.
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ sh.Name & "!A1", TextToDisplay:=sh.Name
Create a hyperlink to current worksheet sh in active cell.
ActiveCell.Offset(1, 0).Select
Select next cell below active cell.
End If
Next sh
Go back to the "For each" statement and store next worksheet in sh worksheet object.
End Sub
Stop macro
Back to top
1.4 Where to put the code?
- Press Alt+F11 to open the Visual Basic Editor (VBE).
- Press with the right mouse button on your workbook name in the project explorer, see image above.
A context menu appears. - Press "Insert" and another popup menu appears.
- Press "Module" to insert a new module.
- Copy and paste the VBA macro code above to the newly created code module in your workbook.
1.5 How to run the macro
- Select a cell where you want to begin inserting hyperlinks.
- Press Alt + F8 to view macros.
- Select CreateLinksToAllSheets and then press with left mouse button on the "Run" button.
1.6 Get Excel file
2. Create links to all visible worksheets in a workbook
The image above shows the output of a macro named CreateLinksToAllVisibleSheets. There are two hidden worksheets in this workbook: "hidden" and "Annual report 2011".
The macro returns only hyperlinks of visible worksheets in column A, see the image above.
'Name macro
Sub CreateLinksToAllVisibleSheets()
'Dimension variables and declare data types
Dim sh As Worksheet
'Go through all worksheets in the current workbook
For Each sh In ActiveWorkbook.Worksheets
'Check if current worksheet name is not equal to the worksheet in variable sh AND is visible
If ActiveSheet.Name <> sh.Name And sh.Visible = True Then
'Create hyperlink in the selected cell
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
'Select next cell below
ActiveCell.Offset(1, 0).Select
End If
'Continue with next worksheet
Next sh
End Sub
2.1 Get Excel file
3. Create links to all hidden worksheets in a workbook
The image above shows a workbook with two hidden worksheets "hidden" and "Annual report 2011". The macro created two hyperlinks in cells A2:A3.
The hyperlinks don't work, the worksheets are hidden and you can't navigate to those worksheets unless you unhide them.
'Name macro
Sub CreateLinksToAllHiddenSheets()
'Dimension variables and declare data types
Dim sh As Worksheet
'Go through all worksheets in the current workbook
For Each sh In ActiveWorkbook.Worksheets
'Check if current worksheet name is not equal to the worksheet in variable sh AND is not visible
If ActiveSheet.Name <> sh.Name And sh.Visible = False Then
'Create hyperlink in the selected cell
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
'Select next cell below
ActiveCell.Offset(1, 0).Select
End If
'Continue with next worksheet
Next sh
End Sub
3.1 Get Excel file
Recommended articles
Recommended articles
In this post, I am going to demonstrate how to automatically create a new sheet in the current workbook and […]
Recommended articles
The following macro inserts a new sheet to your workbook and lists all Excel defined Tables and corresponding Table headers […]
4. Creates links to all sheets, tables, pivot tables and named ranges
This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, Pivot tables, Excel defined Tables and named ranges.
The hyperlinks allow you to navigate quickly to any of the Excel objects mentioned above.
What you will learn in this article
- Iterate through worksheets in the active workbook.
- Iterate through Pivot Tables in the active worksheet.
- Iterate through Excel defined Tables in the active worksheet.
- Iterate through named ranges in the active workbook.
- Create hyperlinks programmatically.
- Change column width so the content fits.
- Save text value to a cell using VBA.
- Bold cell text programmatically.
- Select the next cell below using VBA.
How to use macro
The animated image above demonstrates the macro.
- Select a destination cell on a worksheet you want to populate.
- Go to tab "Developer" on the ribbon.
- Press with left mouse button on the "Macros" button and the Macro dialog box appears.
- Select macro CreateToC.
- Press with left mouse button on "Run" button.
- The macro creates hyperlinks to all worksheets, pivot tables, Excel defined Tables and named ranges in the active workbook.
- The macro ends.
VBA code
'Name macro
Sub CreateToC()
'Dimension variables and declare data types
Dim sh As Worksheet
Dim cell As Range
Dim pt As PivotTable
Dim tbl As ListObject
Dim nms As Name
'Populate selected cell with "Table of Contents"
ActiveCell.Value = "Table of Contents"
'MAke the selected cell bolded
ActiveCell.Font.Bold = True
'Select the next cell below
ActiveCell.Offset(1, 0).Select
'Save text Worksheets to selected cell
ActiveCell.Value = "Worksheets"
'Select the next cell below
ActiveCell.Offset(1, 0).Select
'Iterate through each worksheet in active workbook
For Each sh In ActiveWorkbook.Worksheets
'Make sure the worksheet name is not equal to the currently selected worksheet
If ActiveSheet.Name <> sh.Name Then
'Add hyperlink to selected cell with worksheet name linking the the worksheet
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
'Select the next cell below
ActiveCell.Offset(1, 0).Select
End If
Next sh
'Save text Pivot tables to selected cell
ActiveCell.Value = "Pivot tables"
'Select the next cell below
ActiveCell.Offset(1, 0).Select
'Iterate through all worksheets in active workbook
For Each sh In ActiveWorkbook.Worksheets
'Iterate through all pivot tables in worksheet
For Each pt In sh.PivotTables
'Add hyperlink to selected cell with pivot table name linking to the pivot table
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sh.Name & "'!" & pt.TableRange1.Address, TextToDisplay:=pt.Name
'Select the next cell below
ActiveCell.Offset(1, 0).Select
'Continue with next pivot table
Next pt
'Continue with next worksheet
Next sh
'Save text Tables to selected cell
ActiveCell.Value = "Tables"
'Select the next cell below
ActiveCell.Offset(1, 0).Select
'Iterate through all worksheets in active workbook
For Each sh In ActiveWorkbook.Worksheets
'Iterate through all Excel defined Tables in worksheet
For Each tbl In sh.ListObjects
'Add hyperlink to selected cell with Excel defined Table name linking to the Excel defined Table
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
tbl.Name, TextToDisplay:=sh.Name
'Select the next cell below
ActiveCell.Offset(1, 0).Select
'Continue with next Excel defined Table
Next tbl
'Continue with next worksheet
Next sh
'Save text Named Ranges to selected cell
ActiveCell.Value = "Named ranges"
'Select the next cell below
ActiveCell.Offset(1, 0).Select
'Iterate through all named ranges in workbook
For Each nms In ActiveWorkbook.Names
'Add hyperlink to selected cell with the name of the named range linking to the the named range
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
nms.Name, TextToDisplay:=nms.Name
'Select the next cell below
ActiveCell.Offset(1, 0).Select
'Continue with next named range
Next nms
'Change column widths so they fit the content
ActiveSheet.Columns(ActiveCell.Column).AutoFit
End Sub
Where to put the code?
- Copy above VBA code.
- Press Alt + F11 to open the Visual Basic Editor.
- Select your workbook in the Project Explorer.
- Press with left mouse button on "Insert" on the menu.
- Press with left mouse button on "Module" to insert a code module.
- Paste VBA code to the code module.
- Return to Excel.
For next statement category
Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]
What's on this page Finding the shortest path - A * pathfinding Optimize pick path in a warehouse 1. […]
The User Defined Function (UDF) demonstrated in this article, resizes a given range to columns or rows you specify. This […]
Hyperlinks category
Table of Contents List all hyperlinks in worksheet programmatically Find cells containing formulas with literal (hardcoded) values Extract cell references […]
This article will demonstrate how to create a hyperlink that takes you to the first empty cell below data in […]
The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]
Excel categories
48 Responses to “Create links to all sheets in a workbook programmatically”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
[...] How to create links to all sheets in a workbook [Get Digital Help] [...]
Hello MR Oscar Cronquist!
I have the following query after a long search found some things, but not exactly what I need. I have 25 workbooks with worksheets 8-10. The tables are all quite large. So I found a few macros that do not help me to get the following:
For example: open excel spreadsheet with macro example (book1) and a button to retrieve it (activated) - This workbook is the folder with all the other workbooks, start looking for the word "cucumber" and starts the macro to search all workbooks, worksheets and cells. Then in (book1) as in column A:A to I display exactly what workbook and worksheet which cell is the word or words. After appearing in column A:A search word where I can make a link to a worksheet. Consider the example made of you - how to export worksheets but only for one workbook, but to me it's different. Many thanks in advance.
Example:
C:\Users\Nevi_\Desktop\trii\[Book1.xls]Sheet1
Book4455.xls
Sheet8;E34;G2;B12
Sheet2;A2;b23;AC98
Book12.xls
Which Sheet;Which Cells
Which Sheet;Which Cells
dasds.xls
Which Sheet;Which Cells
Which Sheet;Which Cells
Book124554.xls
Which Sheet;Which Cells
Which Sheet;Which Cells
Bookvsdsdsd.xls
Which Sheet;Which Cells
Which Sheet;Which Cells
Which Sheet;Which Cells
Which Sheet;Which Cells
Which Sheet;Which Cells
Which Sheet;Which Cells
Which Sheet;Which Cells
cucu.xls
Which Sheet;Which Cells
Which Sheet;Which Cells
and next one
and next one
and next one
and next one
Hello Mr. Oscar
that could be a solution to my (for me personally) huge problem?
Thank you
Kosta,
I don´t have an answer for you. Your question is complicated.
Sub Cmdty()
Dim PT As PivotTable
Dim PTcache As PivotCache
Dim pf As PivotField
Dim pi As PivotItem
On Error Resume Next
Sheets.Add
ActiveSheet.Name = "PVT"
Set PTcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A5,DC6577"))
Set PT = ActiveSheet.PivotTables.Add(PTcache, Range("A1"), "Table")
With PT
.AddFields("GROUP").Orientation = xlRowField
.AddFields("QTY").Orientation = xlDataField
.AddFields("STD").Orientation = xlDataField
.AddFields("MR$").Orientation = xlDataField
.AddFields("FCST").Orientation = xlDataField
.AddFields("PPV").Orientation = xlDataField
End With
End Sub
actually I corrected the code:
...
Sheets("PVT").Select
Set PT = ActiveSheet.PivotTables.Add(PTcache, Range("A1"), "Table")
...
Still to no avail... if you will notice my data range is very long... It's very frustrating I cannot work it out on myself. could you help out on this? Thanks in advance
I am trying to do exactly what your macro describes that it will do. When I run the macro, a list of worksheet names is generated, however, the links do not work and instead I receive a pop-up window stating "This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator."
As I am working from a personal computer, and I am the admin, do you happen to know why the links will not work? Or what might prompt this message?
Peter,
https://answers.microsoft.com/en-us/office/forum/office_2007-outlook/cant-open-any-hyperlinks-this-operation-has-been/8815ad6c-0f44-477a-9ffa-beb98109b5a6
Oscar,
Thank you very much! I was absolutely stuck on something like this for work - creating a hyperlink to a new sheet created. With this, I've implemented a update button and all sheets are automatically hyperlinked. Thank you very much!
Hi, Nice to found this awesome post.
Thanks very much on the guide.
May I know how can I make this VB Code become an Excel Add-In?
So that able to use this function in several Excel files.
Thanks.
^Jason^,
You don´t need an add-in.
I have a perfect blog post for you:
Add your personal excel macros to the ribbon
[…] Welcome to the forum, Take a look at this link it may help find a solution for you. Quickly create links to sheets, tables, pivot tables and named ranges in a workbook | Get Digital He… […]
[…] this: Create links to all sheets in a workbook | Get Digital Help - Microsoft Excel resource I want the same, but with the addition of other data such as the total of the first column of each […]
Hello Oscar,
Your code is just what I was looking for to do a project.
Is there something I can add to stop the list from listing hidden sheets?
I only want visible sheets to list
Thank you
KMR
Got it working just had two remember what to add.
I don't use excel that much.
Thank you.
Hi Oscar,
That code was exactly what I was looking for to for my workbook, excellent.
Thanks,
Your code appears to provide hyperlinks to all my ws.
However, when I press with left mouse button on any hyperlink wherein the ws has a space in the name eg: "Juice, orange, concentrated" I get an error msg saying "Reference is not valid."
WS with single names work fine. eg: "Persimmons"
Can you help me? Thank you.
Hi Quinn, Even I have the same issue. Did you find anyway to resolve this.?
[…] How to create links to all sheets in a workbook [Get Digital Help] […]
[…] How to create links to all sheets in a workbook [Get Digital Help] […]
Many thanks to your kindness & very help information
SAMEH
Hi Oscar,
what is the VBA code to create back button/link in each worksheet which should take me to the original page. And it must be created in all sheets at once.
Thanks in advance.
Kranthi
This macro creates a "Back" link in cell A1 on every sheet except the first sheet.
Sub CreateLinksOnAllSheets() Dim sh As Worksheet Dim cell As Range, i As Integer With ActiveWorkbook For i = 1 To ActiveWorkbook.Worksheets.Count If ActiveSheet.Name <> .Worksheets(i).Name Then .Worksheets(i).Hyperlinks.Add Anchor:= _ .Worksheets(i).Range("A1"), Address:="", SubAddress:= _ "'" & ActiveSheet.Name & "'" & "!A1", TextToDisplay:="Back" End If Next i End With End SubThank your Oscar
**You
Which code I might use after having created the Back button/link and I no longer need them in my worksheets Automatically?
Hi, how can i do this for all the worksheets except for the first 4?
Erin
Sub CreateLinksToAllSheets() Dim sh As Worksheet Dim cell As Range, i As Integer With ActiveWorkbook For i = 5 To ActiveWorkbook.Worksheets.Count If ActiveSheet.Name <> .Worksheets(i).Name Then ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "'" & .Worksheets(i).Name & "'" & "!A1", TextToDisplay:=.Worksheets(i).Name ActiveCell.Offset(1, 0).Select End If Next i End With End SubYou are awesome Oscar! Saved me a whole load of time!
This works great. However, when I add new worksheets, it updates the list automatically. But, it puts the back button in cell H1, not A1 for some reason. When I actually run the macro after adding the new worksheets, it also adds the back button to A1 as well.
Tracy T
However, when I add new worksheets, it updates the list automatically.
The macro is not executed if you add worksheets, to do that you need event code to run the macro automatically.
I am not sure what is going on in your workbook.
I want to create link for visible sheet in workbook. Please send me vba code sir
How to start the list in A1 cell rather than activecell?
Clear all the values of A column then return the list to A1 cell instead of activecell?
Leo81457,
Sub CreateLinksToAllSheets() Dim sh As Worksheet Dim cell As Range Worksheets("Overview").Range("A:A") = "" Worksheets("Overview").Range("A1").Select For Each sh In ActiveWorkbook.Worksheets If ActiveSheet.Name <> sh.Name Then ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name ActiveCell.Offset(1, 0).Select End If Next sh End SubHi OSCar,
Can you help me regarding new macro.
i need back macro from all sheets to mail sheets.
ex.
Sheet1 :Main Index
Sheet2: Table first
Sheet3: Table second
i want if we select table first sheet in first row then go to Mail index
and when select second sheet then go to mail sheet but go to mail sheet row no 2.
Regards
Rahul
What is the reason behind declaring cell as range
dim cell as range
please give me explanation for the same because i am on learning stage so i am confused that why cell is declared as range because without declaring cell as range the above code works very well. so why we declare cell as range
please its my request
clear my confusion
please
Pradeep Singh Rawat,
The variable will be Variant type if you don't declare the variable.
It will work, however, it will use more memory than necessary.
https://exceloffthegrid.com/do-you-have-to-use-dim-to-declare-variables/
sir i think i am asking that without this declaration dim cell as range (without this line of code)the above code works very well so why did you use this line of code dim cell as range
e.g
Sub CreateLinksToAllSheets()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If ActiveSheet.Name sh.Name Then
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & sh.Name & "'" & "!A1", TextToDisplay:=sh.Name
ActiveCell.Offset(1, 0).Select
End If
Next sh
End Sub
this code also works very well so what is the need of
Dim cell as range
i think you got my point
Pradeep Singh Rawat,
I have already explained this, declaring variables will save you memory and your macro/udf will be faster.
after pasting the code. which button I have to press for running the code. Please. I have pasted the code by pressing Alt +F11, insering a module. and copying the code. Please guide me how to run the macro.
I want to search PDF file in one drive e.g.(F) from a worksheet in Excel with VBA
pleas send me a code if possible
S.E.M
I found this:
https://software-solutions-online.com/how-to-search-a-pdf-for-a-string-using-vba-for-excel/
Hi! The code is wonderful - thank you so much. Some have asked this but I can't find the answer in comments: how do you run the macro for visible worksheets only? Thanks
Just wanted to say thanks for sharing this fix for updating worksheet hyperlinks. Been doing it manually for some time and didn't have the knowledge to automate. The VB sub updates all of my 250 sheets in seconds when I was previously taking well over an hour. Looks like this has been out there for sometime and was lucky to find. Thanks again.
I am happy you find it useful, thank you for commenting.
I Want to do hyperlink of all visible sheets from other workbook.
Hi Oscar! Just wanted to first start off and thank you for the wonderful code, and help youre providing for many people out on the web trying to learn atleast some basic vba and code.
I tried your linktoallvisiblesheets, and it worked wonderful. I just had one follow-up question though, is it possible to insert a unique ID- handler in each set in just that code, in say column B, or any other column as well?
It works great for my need and when inserting a new sheet, but it would be great with some sort of Unique ID- per row since im using it for a log?