Link question

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Using Excel 2003 - I have two tabs one has detail info for unit cost; the second has summery info for each unit. What I'd like to do is if you are on the summery tab and click on a unit number, then if you go to the detail tab it will be at the first unit with that number.

https://authentification.site/files/22301458/Tab_to_Tab_link.xls

Thanks,
Brad

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am not sure exactly what you asked ( though your question was very simple). It seem that you would using find with macro, that you would need to define an event for your summary sheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Assumptions:
1. The summary sheet is called Detail
2. The summary sheet is named Sheet1 in VBE

steps:
1. Press ALT + F11 to goto VBE
2. Press CTRL + R to open Project Explorer
3. Double click on your Summary sheet
4. Paste this code

Public lLocation As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Column <> 1 Then Exit Sub
    
    On Error Resume Next
        lLocation = 0
        lLocation = Sheets("Detail").Cells.Find(Target, Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row
    On Error GoTo 0
    
End Sub


5. Double click on your Detail Sheet and paste this

Private Sub Worksheet_Activate()

    If (Sheet1.lLocation <> "" And Sheet1.lLocation > 0) Then
        Cells(Sheet1.lLocation, "a").Select
        Sheet1.lLocation = ""
    End If
        
End Sub
0
WOW!! that works great! Thanks for the help!!!

Regards,
Brad
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
you are welcome Brad. You have doing some thing very rare, which is you have posted a feedback.once getting the answer. Not many do that. So thank you for doing that. :)
0