PC Review


Reply
Thread Tools Rate Thread

(Complex) Creating hyperlinks to sheets

 
 
=?Utf-8?B?a2x5c2VsbA==?=
Guest
Posts: n/a
 
      2nd Aug 2007
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
 
Reply With Quote
 
 
 
 
=?Utf-8?B?V2lnaQ==?=
Guest
Posts: n/a
 
      3rd Aug 2007
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

 
Reply With Quote
 
=?Utf-8?B?a2x5c2VsbA==?=
Guest
Posts: n/a
 
      3rd Aug 2007
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help on creating complex queries Raveendiran RR Microsoft Access Queries 0 30th Sep 2009 05:36 PM
Complex Function: Match names on Two Sheets ryguy7272 Microsoft Excel Worksheet Functions 2 17th Dec 2008 07:26 PM
Creating a complex search echo_park Microsoft Excel Worksheet Functions 3 4th Aug 2006 11:45 AM
Creating hyperlinks to protected excel sheets =?Utf-8?B?RGVhbg==?= Microsoft Word Document Management 1 10th May 2005 02:30 PM
Complex? Simple? Filtering Sheets Neil Microsoft Excel Worksheet Functions 1 5th Aug 2003 03:45 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:14 PM.