Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Create Hyperlinked Index of Sheets in Workbook
The ability to generate an index of sheets within a workbook is one of Excel's important capabilities. Excel is a strong tool for managing and organising data. By using this index as a convenient navigation tool, you may easily switch between several sheets with a single click. In this tutorial, we'll walk you step-by-step through the process of making a hyperlinked Excel index. By the time you finish this tutorial, you will know how to organise your workbooks' index so that it is simple to browse through a number of sheets.
This article will provide you the knowledge you need to increase your productivity and efficiency when working with several sheets, regardless of how skilled you are with Excel. In order to create a hyperlinked index of sheets in Excel, let's get started.
Hyperlinked Index of Sheets in Workbook
Here we will first insert a VBA module, then run it to complete the task. So let us see a simple process to know how you can create hyperlinked indexes of sheets in a workbook in Excel.
Step 1
Consider any Excel sheet.
First, right-click on the sheet name and select View Code to open the VBA application.
Step 2
Then click on Insert, select Module, and copy the below code into the text box.
Insert > Select Module > Copy.
Example
Sub CreateIndex()
Dim xAlerts As Boolean
Dim I As Long
Dim xShtIndex As Worksheet
Dim xSht As Variant
xAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
On Error Resume Next
Sheets("Index").Delete
On Error GoTo 0
Set xShtIndex = Sheets.Add(Sheets(1))
xShtIndex.Name = "Index"
I = 1
Cells(1, 1).Value = "INDEX"
For Each xSht In ThisWorkbook.Sheets
If xSht.Name <> "Index" Then
I = I + 1
xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
End If
Next
Application.DisplayAlerts = xAlerts
End Sub
Step 3
Then you click on F5 to run the module. Then you can see that all the cells below the index will be hyperlinked.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can create hyperlinked indexes of sheets in a workbook in Excel to highlight a particular set of data.
136 Views
