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.
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
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.