(repost) Adding hyperlinks to VBA-generated worksheets
 

Hi,

I have code that generates worksheets from a list of worksheet names
inputted into either Column C or Column D. I would like to add hyperlinks
that link the values in either Column C or Column D (whichever column the
user chooses to enter the name) to the respective sheet that was generated.

The actual worksheet name entered into one of the two columns creates a
series of 6 pivot tables, and this entered value becomes their page filters.
All I want to
do is create the hyperlink to the generated sheets.

Here is the code that creates the sheets:

Worksheets("PIV_Template").Copy _
After:=Worksheets(Worksheets.Count)

Sheets("PIV_Template").Visible = False

Set sh = ActiveSheet
sh.Name = Target
For Each pt In sh.PivotTables
With pt
If Target.Column = 3 Then
With .PivotFields("ITBGrp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
.CurrentPage = pi.Value
End If
Next
End With
ElseIf Target.Column = 4 Then
With .PivotFields("IO_Grp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
.CurrentPage = pi.Value
End If
Next
End With
End If
End With
Next

I do know that the code below did the trick in another solution that I
developed previously, but I can't figure out how to adapt it to the code
above.


cell.Hyperlinks.Add Anchor:=cell, _
Address:="", _
SubAddress:="'" & ActiveSheet.Name & "'!A1", _
TextToDisplay:=cell.Value

Any help would be
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557