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