Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding related data in Excel 2000 worksheets (Excel 2000)

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding related data in Excel 2000 worksheets (Excel 2000)

    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>


  3. #3
    New Lounger
    Join Date
    Nov 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding related data in Excel 2000 worksheets (Excel 2000)

    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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •