This took me a couple of hours to get working, which means that one of two things happened.  I am very rusty in my VB or nothing I found online made sense.  I’m going with a little bit of the former and a lot of the latter.

The problem I was facing is that I wanted to create an Excel dashboard which linked worksheets in the file.  I wanted to do this dynamically using VBA in order to be able to get a snapshot of the whole workbook without having to go sheet by sheet.

The final code looks like this:

   1:  LinkToSheet = "'" & Sheets(i).Name & "'!A1"

   2:  NewSheet.Hyperlinks.Add Anchor:=NewSheet.Cells(10 + c, 6), Address:="", 

   3:  SubAddress:=LinkToSheet, ScreenTip:="", TextToDisplay:=""

Now to break it down a bit for those of us who need a little help with VB.  This part of the code dictates the worksheet where the hyperlink will go, in this case I have a variable called NewSheet:

   1:  NewSheet.Hyperlinks.Add

The Anchor property is the location of where to put the hyperlink.  In my case it’s dynamic, but for most people it might be a static location (e.g. F2) like this:

   1:  NewSheet.Hyperlinks.Add Anchor:=F2

The Address property needs to be blank if you’re linking to other worksheets, you use this if you’re linking to other files or URLs.  The LinkToSheet variable is used because some of the worksheet names I use have spaces, so I need to be able to format my worksheet as ‘WorkSheet Name’!A1.  Finally, the last two properties are more adornments than anything else in case you have to have a hover tip or display different text than the name of the worksheet.

~david