Adding hyperlinks to macro-generated sheets from list of sheet nam

Hi,

I have code that generates sheets from a list in a column. I would like to
add hyperlinks that link the values in Column C or Column D (whichever the
user enters in a drop-down code) to the respective sheet which was generated.

This is the simplified version. The actual code create a series of 6 pivot
tables using this value in Column C or D as their page filters. All I want to
do is create the hyperlink to the generated sheet as well.

Here is the code that I have so far:

>Private Sub Worksheet_Change(ByVal Target As Range)
>Dim pi As PivotItem
>Dim sh As Worksheet
>Dim pt As PivotTable

>ActiveWorkbook.PrecisionAsDisplayed = False
>Application.DisplayAlerts = False
>Application.ScreenUpdating = False
> With Application
> .Calculation = xlManual
> .MaxChange = 0.001
> End With
>
>If Target.Count > 1 Then Exit Sub
>If Len(Trim(Target)) = 0 Then Exit Sub
>If Target.Column = 3 Or Target.Column = 4 Then
>
>Sheets("PIV_Template").Visible = True
>
>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
>
>sh.Activate
>End If
>Call Format_Grouping_Titles
>Call Format_Columns
>Call Format_Titles
>
>ActiveWorkbook.PrecisionAsDisplayed = True
>Application.DisplayAlerts = True
>Application.ScreenUpdating = True
> With Application
> .Calculation = xlAutomatic
> .MaxChange = 0.001
> End With
>
>End Sub


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 very much appreciated!
Kent.
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557