link question Solved/Closed

Question

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 

Configuration: Windows XP / Internet Explorer 8.0

rizvisa1 · Answer

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.

rizvisa1 · Answer

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

Link question

2 responses

Similar discussions

Subscribe To Our Newsletter!