CODEWITHSUNDEEP
vbaexcelhyperlink
The Silent Cartographer
The Silent Cartographer

Reputation: 63

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

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.

Upvotes: 3

Views: 31614

Answers (4)

Michal Knytl
Michal Knytl

Reputation: 11

In my case I experienced "Hyperlink is not valid" error when I clicked to hyperlink created by Portlands Runner code. Surrounding and joining Sheets("Sheet1").Name by aphostrophes resolved that problem and that error was gone. If you experience the same, then use it like this:

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

Upvotes: 1

user5293686
user5293686

Reputation: 1

These are the ones I use to create hyperlinks on VBA to a URL or a specific spreadsheet

Sheets("RogueOne").Hyperlinks.Add Anchor:=Sheets("RogueOne").Cells(3, 4), Address:="http://wired.com", ScreenTip:="Wired", TextToDisplay:="WIRED"
    Sheets("RogueOne").Hyperlinks.Add Sheets("RogueOne").Cells(5, 5), "", Sheets("RogueTwo").Name & "!c2", "", "RogueTwo"

Upvotes: 0

NewLearner
NewLearner

Reputation: 159

Perhaps you could try this...

Dim WS as Worksheet
ActiveSheet.Hyperlinks.add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & WS.Name & "'" & "!A1", TextToDisplay:=WS.Name

Because when you look at the link, it usually shows 

file:///name'WS.name'!A1

and it could be possible that the pair of ' ' may have been omitted.

Upvotes: 0

Automate This
Automate This

Reputation: 31394

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.

Upvotes: 4

Related Questions