Macros for Copying Data of Cell even after increasing Number of rows and applying filter

[tricksfire]

Hey,I have a sheet of Engineers Name with allocated complaint numbers and their mobile number. i want to create a Macros script so that every time i apply filter on a particular engineer and press a Macros Created Keyboard short cut it copies engineer mobile number.

Engineer Mobile Number: Column H
Engineer Name: Column W

 

[My Aswer Is This]

Try this:

Sub Get_Phone_Number()
On Error GoTo M
Dim ans As String
ans = InputBox("Enter Name")
Dim SearchString As String
Dim SearchRange As Range
SearchString = ans
Set SearchRange = Range("W1:W" & Cells(Rows.Count, "W").End(xlUp).Row).Find(SearchString)
ansNumber = SearchRange.Offset(0, -15).Value
MsgBox ans & "  Phone # is:" & vbNewLine & ansNumber
Application.ScreenUpdating = False
Exit Sub
M:
MsgBox "No such name found." & vbNewLine & "You must enter full name" & vbNewLine & "You entered:" & vbNewLine & ans
Application.ScreenUpdating = False
End Sub

 

[tricksfire]

Thanks for quick reply, I think above code works similar as CLT+F to find and move to defined cell. But what i am trying to find is completely different. As i said above,I have an Excel sheet with Engineers Name in Column W and Engineer mobile number in Column H . I Am Looking for some tricks or macros script so that if i apply filter and select a particular engineer name from the list in Column W and press a Macros Created Keyboard shortcut it copies engineer mobile number Column H.

 

[My Aswer Is This]

I thought you were wanting to look up some ones phone number.

Sorry but now I do not understand what you want.