VBA Hyperlink and populate

[sriche01]

Hello all,

I have a code question and don't know where to start to make my idea work.

I have two worksheets. The first is a static list of Item Numbers approximately 400 rows or so long. This sheet is "Sales Plan". Second is an analysis of an individual item number. This sheet is "Profile Path".
I want the individual item numbers in "Sales Plan" column A to be a hyperlink that does 3 things when one is clicked:
1. Open a new window.
2. Navigate to 'Profile Path'!D3
3. Populate 'Profile Path'!D3 with the value of the cell that was clicked.

I assume I have to start with the sheet code of Sales Plan.

Can anybody get me started on this?
Thanks in advance and Merry Christmas too!

 

[sriche01]

Ok, what I have so far is in the code for Sales Plan worksheet

Option Explicit



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If Selection.Count = 1 Then
        If Not Intersect(Target, Range("Sales_Plan_Item")) Is Nothing Then
            ActiveWorkbook.NewWindow
            Application.Goto Reference:=ActiveWorkbook.Worksheets("Profile Path").Range("D3"), Scroll:=False
            
        End If
    End If
End Sub

"Sales_Plan_Item" is a named range that refers to the list of cells I want to click to run the macro.

So far, this opens a new window, then goes back to the first window and navigates to 'Profile Path'!D3.

How do I get it to navigate within the new window rather than the old?

 

[sriche01]

So I got everything to work EXCEPT doing everything in a new window. This is my code. If it could be better let me know please!

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim itm As Range
Dim rng As Range
 If Selection.Count = 1 Then
        If Not Intersect(Target, Range("Sales_Plan_Item")) Is Nothing Then
            'somewhere in here I'd like a command to create a new window in which to run the
            'code below
            Set itm = Application.Selection
            Set rng = ActiveWorkbook.Worksheets("Profile Path").Range("D3")
            rng.Value = itm.Value
            Application.Goto Reference:=ActiveWorkbook.Worksheets("Profile Path").Range("D3"), Scroll:=False
        End If
    End If
End Sub

Again, the above code is in the sheet code of 'Sales Plan' and works. But, I would like it to open a new window when a cell within the range is clicked, and in that window go to the cell in 'Profile Path' and change it.

Thanks!