Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

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

Report
Posts
4
Registration date
Thursday April 27, 2017
Status
Member
Last seen
May 23, 2017
 -
Posts
4
Registration date
Thursday April 27, 2017
Status
Member
Last seen
May 23, 2017
 -
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

Answer 1 / 1
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
 462
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
Report
Posts
4
Registration date
Thursday April 27, 2017
Status
Member
Last seen
May 23, 2017
Thanks...
Report
Posts
4
Registration date
Thursday April 27, 2017
Status
Member
Last seen
May 23, 2017
I consider this as [SOLVED]
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!