Search for a value in column and copy to different sheet

Solved/Closed
raghuprabhu
Posts
4
Registration date
Thursday April 27, 2017
Status
Member
Last seen
May 23, 2017
- Updated on May 23, 2017 at 12:06 PM
raghuprabhu
Posts
4
Registration date
Thursday April 27, 2017
Status
Member
Last seen
May 23, 2017
- 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...

1 reply

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
512
May 23, 2017 at 12:05 PM
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
0
raghuprabhu
Posts
4
Registration date
Thursday April 27, 2017
Status
Member
Last seen
May 23, 2017

May 23, 2017 at 07:06 PM
Thanks...
0
raghuprabhu
Posts
4
Registration date
Thursday April 27, 2017
Status
Member
Last seen
May 23, 2017

May 23, 2017 at 07:07 PM
I consider this as [SOLVED]
0