(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
|