Finding related data in Excel 2000 worksheets (Excel 2000)

[PaulV]

I have a workbook that has two work sheets. Work sheet one has a column containing ten digit service order numbers and the invoicing data related to these service orders. Work sheet two has a column with the same service order numbers but data relating to the hours spent by technicians on the service orders, I would like a user to be able to select a cell with a service order number in one work sheet and then trigger a macro via hot keys to go to the same service order number in the other work sheet to view the other data. I have tried to do this via the macro recorder by copying the selected service order number, switching do the next work sheet, going to edit -.> find, paste and then find. However this only finds the service order number used when I created the macro. Can someone please help with a way I can make this work?

[sdckapr]

Here is something relatively simple. Assumes the 2 sheets are "sheet1" and "sheet2" change names a appropriate.

It looks for the active cell in either one of these sheets in column A (change as approrpriate) of the other sheet, if found it goes there, if not it stays where it is
Steve

<pre>Option Explicit
Sub ActivateInOtherSheet()
Dim sFind As String
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim wksGoto As Worksheet
Dim wksCurr As Worksheet
Dim rFound As Range
Application.ScreenUpdating = False
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")
Set wksCurr = ActiveSheet
sFind = ActiveCell.Value


If wksCurr.Name = wks1.Name Then
Set wksGoto = wks2
ElseIf wksCurr.Name = wks2.Name Then
Set wksGoto = wks1
End If
If Not wksGoto Is Nothing Then
wksGoto.Activate
Set rFound = wksGoto.Range("A:A").Find(What:=sFind, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If rFound Is Nothing Then
MsgBox ("The item selected: " & vbCrLf & _
"[" & sFind & "]" & vbCrLf & _
"is not in " & vbCrLf & _
"'" & wksGoto.Name & "'." & vbCrLf & vbCrLf & _
"You will remain on: " & vbCrLf & _
"'" & wksCurr.Name & "'.")
wksCurr.Activate
Else
rFound.Activate
End If
End If

Set rFound = Nothing
Set wks1 = Nothing
Set wks2 = Nothing
Set wksGoto = Nothing
Set wksCurr = Nothing
Application.ScreenUpdating = True

End Sub</pre>

[PaulV]

Made the suggested changes in your post and it worked a treat. Like the professional touch of the message to the user if the match failed.
Many thanks for the prompt response and the successful solution.
Paul <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>