Help with wiriting macros

[jlaessig]

Hi, I am very new to writing macros and am hoping someone with more knowledge and experience can help me! I want to write something that will essentially replace the find and select function between two worksheets. I have a pivot table with sample numbers in column A on a tab called "Test on Time" and I would like to be able to double click a sample number and have it find the same sample number on another worksheet called "KPI Report" and highlight that row.

This may be super easy, but I have no idea. Please help!

Jessica

 

[tonyyy]

Jessica,

Welcome to the Board.

You might consider the following...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sample As String, found As Range
If Not Intersect(Target, Columns(1)) Is Nothing Then
    sample = Target.Value
    Set found = Sheets("KPI Report").Cells.Find(What:=sample, After:=Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not found Is Nothing Then
        Sheets("KPI Report").Activate
        found.EntireRow.Select
    Else
        MsgBox "The search item was not found."
    End If
End If
End Sub

The code should be pasted into the Test on Time sheet module.

Cheers,

tonyyy

 

[jlaessig]

AMAZING!!! works perfectly, thank you!!!

 

[tonyyy]

You're very welcome...