VBA Hyperlink to matched cell on different sheet

[jwdesselle]

Good afternoon everyone. This is one of those 'summary sheet with hyperlinks' questions. I have everything working great, but my hyperlinks all link to !A1 and I would like to change that to the matching cell on the destination sheet.

The code below is part of a loop which takes some of the contents of each sheet and copies onto summary sheet

Set CopyRng = wSheet.Range("B2:D12")

The code below places the hyper link in column D like I want. But, I want to somehow change !A1 below to a formula that matches cell in column C of summary sheet ("EmailSchedule") to corresponding cell on wSheet.Name in column D.

The code snipit below is just a tiny portion of a really long thing. Wish I could post my entire workbook, but will see how far I can get with this.

Thanks!

'Create hyperlinks to corresponding tab
'If number of messages changes on Monthly tabs, increase the number on the For line below
Dim i1 As Integer
For i1 = 1 To 11
'Range("D:D").Cells.SpecialCells(xlCellTypeLastCell).Row
ActiveCell.Hyperlinks.Add Anchor:=.Range("D" & CStr(i1)), Address:="", _
SubAddress:="'" & wSheet.Name & "'!A1", TextToDisplay:=wSheet.Name
Next i1

 

[jwdesselle]

I've uploaded the document here: https://1drv.ms/x/s!ApPiurzPkMTSj5UfxBKmoPwxXDtdcA