ExcelBanter - (Complex) Creating hyperlinks to sheets

Hi,

I've posted this question four times and I'm wondering if anyone can answer
it or if I should forget about hyperlinking.

Here it goes once again: Basically, the user enters a code in EITHER column
C OR column D and presto, two worksheets are created. Each of the two
worksheets has 3 pivot tables.

I don't know the codes before they are entered by the user (which
subsequently become the name of the worksheet) that are going to be
entered into either column C or D, but I do know that they are part of named
range comprising the values for my drop-down list. Incidentally, these
values entered by ther user also become the value of the page filter for each
of the pivot tables.

How do I link the value in either column C or D (one value per pair - eg.
either C12 or D12) to the VBA-generated worksheet?

Here is the code that creates the worksheets and pivot tables residing in
them when the user selects a value from the drop-down list:

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

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

If Target.Column = 4 Then

On Error GoTo ITBGrp

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

Set cell = ActiveCell
Set sh = ActiveSheet
sh.Name = Target
sh.Tab.ColorIndex = 43
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

End If
Next
End With
End With
Next

Call Formatting

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

Set sh = ActiveSheet
sh.Name = Target & "-Deliverables"
sh.Tab.ColorIndex = 33
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
End If
Next
End With
End With
Next

Call Formatting

ElseIf Target.Column = 5 Then

On Error GoTo IO_Grp

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

Set sh = ActiveSheet
sh.Name = Target
sh.Tab.ColorIndex = 23
For Each pt In sh.PivotTables
With pt
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 With
Next

Call Formatting

End If
End If
End With

Exit Sub

ITBGrp:
MsgBox "You have entered a duplicate or invalid ITBGrp code."

Sheets("PIV_RC (2)").Select
ActiveWindow.SelectedSheets.Delete
Application.Goto "Summary_Home"

Exit Sub

IO_Grp:
MsgBox "You have entered a duplicate or invalid IO_Grp code."

Sheets("PIV_RC (2)").Select
ActiveWindow.SelectedSheets.Delete
Application.Goto "Summary_Home"

End Sub

Does anyone have any answers of how I can add a hyperlink when I don't know
the
name of the macro-created tab in advance?

Thanks very much,

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