Create links to all sheets in a workbook
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.
- Copy and paste the VBA macro below to a code mode in your workbook. More detailed instructions below.
- Select a cell where you want to begin inserting hyperlinks.
- Press Alt + F8 to view macros.
- Select CreateLinksToAllSheets and then click on Run button.
The macro inserts links to all worksheets in your workbook except the current worksheet.
Watch a video where I explain how to use the macro
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
Explaining vba code
Step 1 - Open VBA Editor
You write macros in the Visual Basic Editor. Open the Visual Basic Editor using one of these instructions:
- Press Alt+F11
- Go to tab Developer and click Visual basic "button"
Step 2 - Create a module
A macro procedure is stored in a code module. Let´s create a module.
- Right click on your workbook in the project explorer.
- Click Insert | Module.
Step 3 - Write macro
Click in code module window. Here you write 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
Recommended articles
List all open workbooks and corresponding sheets [VBA]
In this post, I am going to demonstrate how to automatically create a new sheet in the current workbook and […]
List all open workbooks and corresponding sheets [VBA]
List Excel defined Tables in a workbook [VBA]
The following macro inserts a new sheet to your workbook and lists all Excel defined Tables and corresponding Table headers […]
Did you know that you can select all comments in the current sheet? Press F5, click "Special..." button, select "Comments" […]
Finding the shortest path – A * pathfinding
Two months ago I posted some interesting stuff I found: Shortest path. Let me explain, someone created a workbook that calculated […]
Combine cell ranges ignore blank cells
The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]
The user defined function demonstrated in the animated ggif below, resizes a range you specify to columns or rows you also […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Locate lookup values in an Excel table [HYPERLINK]
Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]
Navigate to first empty cell using a hyperlink formula
This article will demonstrate how to create a hyperlink that takes you to the first empty cell below data in […]
List all hyperlinks in worksheet
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]
41 Responses to “Create links to all sheets in a workbook”
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.
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
[…] 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 click 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.
Thank 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
You 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,
Hi 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 click table first sheet in first row then go to Mail index
and when click 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