Excel VBA Creating a Hyperlink in a different worksheet to a different worksheet in the same workbook

Question

I am working on a project in Excel where I want to validate a data set, whenever two cells in a column have a duplicate I want to write the location of the duplicate to a different worksheet that contains a bunch of information regarding errors in the data.

So my data is in worksheet1 and when I run my code I am writing a hyperlink to some cell in worksheet2 that links to the error in worksheet1.

I found some code which almost accomplishes this task in a different stack question...

Hyperlink to Existing Worksheet in actual Workbook

ActiveSheet.Hyperlinks.Add ActiveCell, "", Sheets(fortnr).Name & "!A1"

However this creates a hyper link in the workbook you are currently in (or is active).

I modified the code a bit, but can't seem to get it to run...

Sheets("Sheet3").Hyperlinks.Add Cells(3, 3), "", Sheets("Sheet1").Name & "!B2", _
"", "Hello"

I have also tried

Sheets("Sheet3").Activate
ActiveSheet.Hyperlinks.Add Cells(3, 3), "", Sheets(fortnr).Name & "!A1"
Sheets("Sheet1").Activate

But it doesn't work. There is no error produced either so I am really at a loss as to why this isn't working.

Solution

Your anchor cell isn't specifying the sheet to anchor to so it defaults to the active sheet.

Change it like this:

Sheets("Sheet3").Hyperlinks.Add Sheets("Sheet3").Cells(3, 3), "", Sheets("Sheet1").Name & "!B2", "", "Hello"

---

Why do you need to have a sheet before .Hyperlinks?

Based on the MSDN documentation here I believe it's because HyperLinks is a method of the sheets class so it's the only way to invoke the method. You can use Activesheet or call any sheet at the start to invoke the method then assign the hyperlink anchor to your desired location.