Report

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

Ask a question raghuprabhu 4Posts Thursday April 27, 2017Registration date May 23, 2017 Last seen - Last answered on May 23, 2017 at 07:07 PM by raghuprabhu
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 
Helpful
+0
plus moins
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 4Posts Thursday April 27, 2017Registration date May 23, 2017 Last seen - May 23, 2017 at 07:06 PM
Thanks...
Reply
raghuprabhu 4Posts Thursday April 27, 2017Registration date May 23, 2017 Last seen - May 23, 2017 at 07:07 PM
I consider this as [SOLVED]
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!