Hyperlinks in VBA Excel 2016

Question

I'm new to VBA, but I feel like there's an easy fix here. The first part of the code works. I need to create a long range of hyperlinks that link to corresponding cells in a different sheet. every thing works well, except it wont link to sheet 2. Please Help.

Answer 1

Please don't use pictures to post a code, nobody can copy that into the VBA editor (modify and test the code).

The Address property (without arguments) of the Range object returns a string of the cell only ($A$1) regardless in which sheet the Range is located.

The property has also a External argument, set it to True to get the full qualified address of the cell.

Address(External:=True)

Andreas.

Answer 2

Thank you.  This is my first time asking any kind community for help, so I'm not sure what I'm doing, I usually just google search for hours until I cab find what I'm looking for. so please exucuse my ignorance, but here is the code again. hopefully you or somebody can help me.

Sub Increment()

Dim link As Worksheet

Dim target As Worksheet

Dim linkCell As Range

Dim iNumber As Integer

Set linkCell = ThisWorkbook.Sheets("Sheet1").Range("A1")

Set link = ThisWorkbook.Sheets("Sheet1")

Set target = ThisWorkbook.Sheets("Sheet2")

For iNumber = 1 To 10

link.Hyperlinks.Add Anchor:=linkCell, _

Address:="", SubAddress:=target.Cells(iNumber, 5).Address, _

TextToDisplay:="Item" & iNumber

Set linkCell = linkCell.Offset(1)

Next iNumber

End Sub

Answer 3

hopefully you or somebody can help me.

As I said: External:=True

Andreas.

Sub Increment()

  Dim link As Worksheet

  Dim target As Worksheet

  Dim linkCell As Range

  Dim iNumber As Integer

  Set linkCell = ThisWorkbook.Sheets("Sheet1").Range("A1")

  Set link = ThisWorkbook.Sheets("Sheet1")

  Set target = ThisWorkbook.Sheets("Sheet2")

  For iNumber = 1 To 10

    link.Hyperlinks.Add Anchor:=linkCell, _

      Address:="", SubAddress:=target.Cells(iNumber, 5).Address(External:=True), _

      TextToDisplay:="Item" & iNumber

    Set linkCell = linkCell.Offset(1)

  Next

End Sub

Answer 4

Thank you so much! it works perfectly. I have no education in VBA so I'm teaching myself. I actually took sections of 3 different codes to paste this code together lol. When you mentioned "Address(External:=True)" I had no idea where to put it, so thanks again and have a good day.