Even though we have Page Up and Page Down shortcut key in excelShortcut Key In ExcelAn Excel shortcut is a technique of performing a manual task in a quicker way.read more to move from one sheet to another. But it becomes complex when we have to move between 10 to more worksheets. It is where the beauty of “Hyperlinks in Excel” comes into the picture. The hyperlink is a predetermined URL that takes you to the respective cell or worksheet as assigned.

We all know how to quickly create hyperlinks in the worksheet to move from one sheet to another. But, of course, you can also go to any other sheet. But in today’s article, we will show you how to create hyperlinks using VBA coding.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Hyperlinks (wallstreetmojo.com)

Let us look at the formula of the hyperlinks in Excel VBA.

  • Anchor: In which cell would you like to create a hyperlink?Address: What is the URL to the hyperlink to navigate?[Sub Address]: What is the location of the page?[Screen Tip]: What is the value shown when you place a mouse pointer on the hyperlink name or cell?[Text to Display]: What test will display in the cell? For example, Worksheet Name.

Assume you want to create a VBA hyperlink to the “Main Sheet” sheet from the other sheet, “Example 1.”

In the worksheet “Example 1” and cell A1, we will create the hyperlink using Code in VBACode In VBAVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Step 1: First, select the cell A1 of the worksheet example 1.

Code:

Sub Hyperlink_Example1()

Worksheets(“Example 1”).Select Range(“A1”).Select

End Sub

Step 2: Now, open hyperlinks using the Active Cell object. Add method.

Sub Hyperlink_Example1()

Worksheets(“Example 1”).Select Range(“A1”).Select

ActiveCell.Hyperlinks.Add(

End Sub

Step 3: The first argument is “Anchor,” i.e., in which cell we would link to create the VBA hyperlink. In this case, cell A1 and since we have already selected cell A1 to mention it as “Selection.”

Sub Hyperlink_Example1()

Worksheets(“Example 1”).Select Range(“A1”).Select

ActiveCell.Hyperlinks.Add(Selection,

End Sub

Step 4: We are not creating any address here, so ignore the Address as of now.

Sub Hyperlink_Example1()

Worksheets(“Example 1”).Select Range(“A1”).Select

ActiveCell.Hyperlinks.Add Anchor:= Selection, Address:="",

End Sub

Step 5: Next is ‘Sub Address.’ Here, we need to mention which sheet we are referring to and the first cell of that sheet.

Sub Hyperlink_Example1()

Worksheets(“Example 1”).Select Range(“A1”).Select

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="‘Main Sheet’!A1",

End Sub

We have mentioned the sheet name as “Main Sheet.” In that sheet cell address is “A1.”

Step 6: Ignore the screen tip as well. For text to display, mention the sheet name.

Sub Hyperlink_Example1()

Worksheets(“Example 1”).Select Range(“A1”).Select

ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="‘Main Sheet’!A1", TextToDisplay:=“Main Sheet”

End Sub

Run this code using the F5 key or manually. Then, it will create a hyperlink in cell A1 in the sheet “Example 1.”

When you click on the hyperlink “Main Sheet,” it redirects to the main sheet.

We have seen creating a VBA hyperlink for one sheet. However, when we have many sheets, it isn’t easy to create a VBA hyperlink for each sheet with the same line of code.

Assume you have 11 worksheets, as shown in the below image.

You want to create a hyperlink for each sheet in the Index sheet using VBA code.

Step 1: Define the variable as a worksheet.

Sub Create_Hyperlink()

Dim Ws As Worksheet

End Sub

Step 2: The first thing is to select the worksheet Index and select cell A1.

Sub Create_Hyperlink()

Dim Ws As Worksheet

Worksheets(“Index”).Select Range(“A1”).Select

End Sub

Step 3: Now, open For Each Loop in VBAFor Each Loop In VBAVBA For Each Loop helps the user to inspect and analyze the groups of objects or values individually. It even facilitates performing the specific activity for every object or value by passing a statement or group of statements in this reference.read more.

Sub Create_Hyperlink()

Dim Ws As Worksheet

Worksheets(“Index”).Select Range(“A1”).Select

For Each Ws In ActiveWorkbook.Worksheets

Next Ws

End Sub

Step 4: Since we have selected cell A1, it is now an active cell. So, start the hyperlink with the active cell.

Sub Create_Hyperlink()

Dim Ws As Worksheet

Worksheets(“Index”).Select Range(“A1”).Select

For Each Ws In ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add(

Next Ws

End Sub

Step 5: An anchor is a hyperlink cell. So, it is the active cell.

Sub Create_Hyperlink()

Dim Ws As Worksheet

Worksheets(“Index”).Select Range(“A1”).Select

For Each Ws In ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell,

Next Ws

End Sub

Step 6: Address is nothing mentioned it as “.

Sub Create_Hyperlink()

Dim Ws As Worksheet

Worksheets(“Index”).Select Range(“A1”).Select

For Each Ws In ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell,Address:="",

Next Ws

End Sub

Step 7: Subaddress: When we loop through the sheet, it should be the sheet name. To refer to the sheet name, we need a single quote, “’” with sheet name and “! Cell Address,” and close the sheet name with a single quote “’.”

Sub Create_Hyperlink()

Dim Ws As Worksheet

Worksheets(“Index”).Select Range(“A1”).Select

For Each Ws In ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell,Address:="",SubAddress:=""& Ws.Name&"!A1"&"",

Next Ws

End Sub

Step 8: Ignore the screen tip. For text to display, you can enter the worksheet name.

Sub Create_Hyperlink()

Dim Ws As Worksheet

Worksheets(“Index”).Select Range(“A1”).Select

For Each Ws In ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & Ws.Name & “!A1” & “”, ScreenTip:="", TextToDisplay:=Ws.Name Next Ws

End Sub

Step 9: To store the hyperlink of each sheet in a different cell, every time a hyperlink, one must create it for one sheet. We need to move down one cell from the active cell.

Sub Create_Hyperlink()

Dim Ws As Worksheet

Worksheets(“Index”).Select Range(“A1”).Select

For Each Ws In ActiveWorkbook.Worksheets ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", SubAddress:="" & Ws.Name & “!A1” & “”, ScreenTip:="", TextToDisplay:=Ws.Name ActiveCell.Offset(1, 0).Select

Next Ws

End Sub

It will create a hyperlink of all the sheets in the Index sheet. This code is dynamic whenever there is any addition or deletion of sheets. Therefore, we need to run this code to have an updated hyperlink.

This article has been a guide to VBA Hyperlinks. Here, we learn how to create hyperlinks in the worksheet using VBA code to quickly move from one sheet to another, along with some simple to advanced examples. Below are some useful Excel articles related to VBA: –

  • Excel Find LinksVBA TodayHyperlink Formula in ExcelConcatenate in VBA