"Lookup, Copy, and Paste" Macros

[rusdiculek]

Guys, I need your help.

I need a macros where I can input a value, and then will looking for a match within a sheet and when corresponding match is found, it will copy the value and it's entire row to another new sheet in which the sheets will be used for all other search.

For example, there is a data consists of hundreds of Student ID, Name, Address, Parents Name. Then I need the macros to input the Student ID, and when I press Enter It will copy the entire data of corresponding student to a new sheet. And it will be repeated (results will be copied in a sheet).

I'm really hoping for a help from Excel Pros Here. Thank you so much!

 

[gallen]

You omit a lot of information like the cell address the user will change to lookup the ID, the range of cells the user will search, the name of the sheet you are copying to.

This code assumes a lot. Mainly it assumes the cell used for searching is ABOVE the student ID's you are searching.

Ensure you change the address of the cell the user changes and the name of the sheet you are copying to:

Private Sub Worksheet_Change(ByVal Target As Range)


    'IMPORTANT: this sub needs to be in the worksheet that need searching


    Dim rIDLookUp As Range 'cell user enters studentID
    Dim rFind As Range 'used when searching the sheet
    Dim sID As String 'student ID to search
    Dim wsCopyTo As Worksheet 'worksheet to copy to
    Dim nr As Long ' row number of next blank row on sheet to copy to
    
    'set the variable to the cell that user will enter ID. Change Address accordingly
    Set rIDLookUp = Range("A1")
    
    'check if user has changed the value of the studentID cell
    If Target.Address = rIDLookUp.Address And rIDLookUp <> "" Then
    
        'set the worksheet to copy info to. Change the Name accordingly
        Set wsCopyTo = Sheets("CopyTo")
    
        'set the string
        sID = rIDLookUp
        
        'find the ID from the bottom up
        Set rFind = Me.UsedRange.Find(What:=sID, After:=Cells(1, 1), SearchDirection:=xlPrevious)
        
        'if we only find the cell we've changed then it wasn't found.
        If rFind.Address = rIDLookUp.Address Then
            MsgBox sID & " not found.", vbInformation, "Student not found"
            Exit Sub
        End If
        
        'get next row to copy to
        nr = wsCopyTo.Range("A" & Rows.Count).End(xlUp).Row + 1
        
        'ignore any events triggered from pasting
        Application.EnableEvents = False
        
        'copy and paste row the row
        Rows(rFind.Row).Copy Destination:=wsCopyTo.Range("A" & nr)
        
        'send focus back to lookup
        Application.Goto rIDLookUp
        
        're-enable events
        Application.EnableEvents = True
        
    End If
   
End Sub

 

[Yongle]

Try this
It is very flexible and searches for anything in any cell
- it will not find a partial match
- the cell value must be an exact match (case is not tested)
- search for ID or name etc

Amend name of original sheet before running the code

Sub FindStudent()
    Dim Student As Range, ws As Worksheet, Original As Worksheet, LookFor As String
    Set Original = Sheets("[I][COLOR=#ff0000]Original Sheet Name[/COLOR][/I]")
    Set ws = Sheets.Add(before:=Sheets(1))
    Original.Range("1:1").Copy ws.Cells(1)
FindNext:
    LookFor = InputBox("What do you want to find?")
    If LookFor = vbNullString Then LookFor = "Nobody"
    Set Student = Original.Cells.Find(what:=LookFor, LookIn:=xlValues, lookat:=xlWhole)
    If Not Student Is Nothing Then
         Student.EntireRow.Copy ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
    Else
        MsgBox "Not Found"
    End If
    If MsgBox("Find another Student?", vbYesNo, "Another search?") = vbYes Then GoTo FindNext
End Sub

As stated by @gallen you have omitted a lot of key information
- the above code assumes that a student ID only appears ONCE on the original sheet
- if there are multiple records for the same ID, then the code requires modification to find all records

 

[rusdiculek]

Thank you so much, Brother @gallen !!! Its perfect, my problem's solved!

 

[rusdiculek]

Thank you Bro @Yongle !! Got it, next time I'll be more specific when asking for help from Pros here