Double click and jump to another sheet with the same data

[feni1388]

Hello..

I need help with macro code.
I have several sheets but the main Master sheet is where everyone put data to check the result.
So within Range D6 to D21, anyone can enter item code.
I want to make it when someone enter an item code and double click it, it will jump to another sheet that has the same item code with its information.

Please help...

Thank you

 

[Peter_SSs]

it will jump to another sheet that has the same item code with its information.

How would we know which sheet that is? Is the item code entered in the Master sheet the name of the sheet that it should jump to?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[feni1388]

Thanks for the reply.
I just updated my account details.

Sorry for the lack of information.
Let's say I have "Master" sheet and "Item" sheet.
In the item sheet on column A I have a list of items' code (around 500 items) and column B, C and so on will have each item's size, weight etc

when someone enter item code GL43 in Master sheet (anywhere between D6 and D21) and double click it, it will jump to "Item" sheet column A where GL43 is located so they can check GL43 weight, size, etc

I hope it's clear. Please let me know if there's something I'm missing.

 

[Peter_SSs]

Ah, thanks. I had thought there were more sheets than that.

Try this in the 'Master' worksheet's module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim rFound As Range
  
  If Not Intersect(Target, Range("D6:D21")) Is Nothing Then
    If Len(Target.Value) > 0 Then
      Cancel = True
      Set rFound = Sheets("Item").Columns("A").Find(What:=Target.Value, LookAt:=xlWhole)
      If rFound Is Nothing Then
        MsgBox Target.Value & " not found"
      Else
        Application.Goto Reference:=rFound, Scroll:=True
      End If
    End If
  End If
End Sub

 

[feni1388]

Thank you so much.
It worked prefectly as I wanted.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[feni1388]

Sorry Peter....
One more thing, if it's possible.
Instead of just jumping to Column A where the item is located, is it possible to highlight the whole row?
As we have a long list, I'm just worried that we're looking at the wrong row.

Thank you in advance

 

[Peter_SSs]

Instead of just jumping to Column A where the item is located, is it possible to highlight the whole row?

I'm wondering why that is needed? The code provided jumps to the correct row and moves that row to the very top of the screen so the relevant row should be very easy to identify and follow across.

If the row is to be highlighted ..

1. When should it become un-highlighted (so in future there are not 2, 3 or more rows all highlighted)?
2. Is there anything on the 'Item' sheet that already has colour applied (Manually, by conditional formatting or by other code)?

 

[feni1388]

Sorry, perhaps I'm not saying it correctly.
What I meant is not highlight with color, but highlight just like when you click on the whole row.

 

[Peter_SSs]

OK, I misinterpreted your request.

Try this change

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim rFound As Range
  
  If Not Intersect(Target, Range("D6:D21")) Is Nothing Then
    If Len(Target.Value) > 0 Then
      Cancel = True
      Set rFound = Sheets("Item").Columns("A").Find(What:=Target.Value, LookAt:=xlWhole)
      If rFound Is Nothing Then
        MsgBox Target.Value & " not found"
      Else
        Application.Goto Reference:=rFound, Scroll:=True
        rFound.EntireRow.Select
      End If
    End If
  End If
End Sub