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

Kent,

AFAIK, you can't. But hopefully for you, someone can get around this.


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"klysell" wrote:

> 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

Thanks. I'll keep trying.. :-)+
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Wigi" wrote:

> Kent,
>
> AFAIK, you can't. But hopefully for you, someone can get around this.
>
>
> --
> Wigi
> http://www.wimgielis.be = Excel/VBA, soccer and music
>
>
> "klysell" wrote:
>
> > 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