Search for a value in column and copy to different sheet [Solved/Closed]

raghuprabhu 4 Posts Thursday April 27, 2017Registration date May 23, 2017 Last seen - May 19, 2017 at 04:48 AM - Latest reply: raghuprabhu 4 Posts Thursday April 27, 2017Registration date May 23, 2017 Last seen
- May 23, 2017 at 07:07 PM
Hi

I want to search for an employee ID in column EID and copy the whole record into another worksheet within the same workbook if the EID is found. If not found give a message saying "No EID found"

Thanks...
See more 

3 replies

TrowaD 2356 Posts Sunday September 12, 2010Registration dateModeratorStatus May 24, 2018 Last seen - May 23, 2017 at 12:05 PM
0
Thank you
Hi Raghuprabhu,

You say:
'copy the whole record into another worksheet'
I interpreted whole record as whole row.
Put the name of 'another worksheet' on code line 5 (=Sheet2 now)

You also didn't specify the column where the EID's are located. Adjust the column letter in code line 6.

Apply changes and give the following code a try:
Sub RunMe()
Dim fValue As Range
Dim sColumn, ShName As String

ShName = "Sheet2"
sColumn = "A"

svalue = InputBox("What is the EID to look for?:")

Set fValue = Columns(sColumn).Find(svalue)
If fValue Is Nothing Then
    MsgBox "No EID found"
Else
    fValue.EntireRow.Copy _
    Sheets(ShName).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If

End Sub


Best regards,
Trowa
raghuprabhu 4 Posts Thursday April 27, 2017Registration date May 23, 2017 Last seen - May 23, 2017 at 07:06 PM
Thanks...
raghuprabhu 4 Posts Thursday April 27, 2017Registration date May 23, 2017 Last seen - May 23, 2017 at 07:07 PM
I consider this as [SOLVED]