Trying to Find selected cell content within another worksheet cell range.

Hi

I have a large SS containing details of 600+ artworks, adjacent to which I have multiple worksheets each containing details of where particular paintings are on display.

Each item has a unique 'Ref No'. What I want is to select the cell containing the 'Ref No' then activate a macro via keystroke.

I need the macro to copy the 'Ref No', select the range of cells on my main worksheet and then 'Find' that unique 'Ref No' and go to it.

I have a simple macro to select the cell range on the main worksheet and open the Find dialog however I can't get the copy/paste into the Find dialog to work? What I have to do is manually type the 'Ref No' into the Find dialog and press Enter, I would like to automate this process.

Sub Macro1()
'
' Macro1 Macro
'

    Application.Goto Reference:="a_All"
    Application.SendKeys "^f"


End Sub

I have attached a representative example of what I am trying to achieve.

Any help much appreciated!

Hello there try this

Dim x As String

x = ActiveCell.Value   'set the value of the activecell, unique ref number cell selected to x
With Sheets("Detail List")    'with the worksheet Detail List
    .Select  'select the worksheet
    .Cells.Find(what:=x, after:=.Range("A1")).Activate   'find the cell whose value is equal to x and activate it
End With

to insert this code into your workbook

1. Press Alt+F8 on your keyboard.
2. Clear the macro name box and enter FindRefNo
3. Then select the Create option
4. In between the Sub FindRefNo( ) and End Sub copy and paste the above code.
5. Exit out of Visual Basic.
6. Press Alt+F8 again
7. Select the FindRefNo macro and then selection Options
8. Assign a Ctrl+ key to your macro. For this example place q in the empty box next to Ctrl+
9. Select Okay and close the macro window.
10. Select the cell whose ref number you want to search for and then hit Ctrl+q on your keyboard.

Let me know if this works for you!

Thanks!

update the code as follows:

Dim x As String

x = ActiveCell.Value   'set the value of the activecell, unique ref number cell selected to x
With Sheets("Detail List")    'with the worksheet Detail List
    .Select  'select the worksheet
    .Range("A1:AB2272").Find(what:=x, after:=.Range("A1")).Activate   'find the cell whose value is equal to x and activate it
End With

WOW that's awesome! does it in milliseconds now.

Thank you very much!

No problem! Don't forget to mark this thread solved and maybe give me a little star tap if I helped!

Thanks!

How do i close it as solved? I've looked but can't see how?

Ok many thanks!