PDA

View Full Version : Range Position and link to ranges



220coupe
03-26-2009, 08:09 AM
Hi, first post here - i have tried searching but cant find what i need.

I am using excel 2007 in compatability mode as the end recipients are using 2003.

I have created a work book and on the first page I have a table of hyperlinks that link to several named ranges in a second sheet. What I am looking to do is, when ever a link is followed, the named range is positioned as though it it was in A1. The ranges for the cells are only one cell in size, and are across row 1, underneath each of the ranges are pivot tables. So for example the following cells are named ranges a1, w1, aa1, ac1. when a user selects the link for AC1 i want AC1 to be in the top left corner of the excel window.

Hope you can help.

Thanks in advance.

Kenneth Hobs
03-26-2009, 11:33 AM
Welcome to the forum!

Right click the sheet that it would goto, View Code, and paste:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> Range("AC1").Column Then Exit Sub
With Application
.EnableEvents = False
.Goto Target, True
.EnableEvents = True
End With
End Sub

p45cal
03-26-2009, 01:29 PM
or right-click in the sheet that has the table of hyperlinks and paste this:Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.Goto Selection, True
End Sub

220coupe
03-30-2009, 02:46 AM
Thanks very much for the help.