(repostX4) Adding hyperlink to macro-generated pivot tables
 

Hi,

I'm trying to create a hyperlink each time a user enters in a code in either
column C or D that ilnks this code in the active cell to the respective
VBA-generated pivot table.

Here is a fragment of the code that I have so far. You can see that my lame
attempts aren't working. This is the third time that I've posted this
question as I am determined to solve my problem.

Dim pi As PivotItem
Dim sh As Worksheet
Dim pt As PivotTable
Dim cell As Range

If Target.Count 1 Then Exit Sub
If Len(Trim(Target)) = 0 Then Exit Sub
If Target.Column = 3 Or Target.Column = 4 Then

If Target.Column = 3 Then

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

Set cell = ActiveCell
Set sh = ActiveSheet
sh.Name = Target
For Each pt In sh.PivotTables
With pt
With .PivotFields("ITBGrp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
..CurrentPage = pi.Value

With sh
..Hyperlinks.Add Anchor:=.Range("Cell"), Address:="", _
SubAddress:="pi.value", TextToDisplay:=pi.Value
End With

End If
Next
End With
End With
Next

Thanks in advance for anyone's amazingly appreciated help!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557