Excel macro to search a txt then copy 1cell and paste it in a new row in sheet 2

Question

I have a huge excel sheet (more than 11K rows and just 11 columns) for customers. I'm trying to print ONLY names of an 1800 out of my 11K customers whom haven't received their ID cards which I have them in my office. Each ID card have a unique bar-code number, which is included in my excel sheet column 'H' And their names are in column 'F'

What I want to do is:

a msg box to enter each ID card bar-code using my bar-code reader. then ,searching in 'H' column , after finding the required customer, then copy what in 'F' column which is the name of the customer and past it to a NEW ROW in sheet 2.

So

Is there a function or a macro in excel sheet which could help me to do that? Or anyone have a better idea of doing that? I tried to recording a macro, but didn't work fine :'( any help would be appreciate

Kind regards :)

this code code do my job, but how could I change the fixed string to an input box, so I search for what I seeking for

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 4

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

  'If value in column E = "Mail Box", copy entire row to Sheet2
  If Range("E" & CStr(LSearchRow)).Value = "Mail Box" Then

     'Select row in Sheet1 to copy
     Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
     Selection.Copy

     'Paste row into Sheet2 in next row
     Sheets("Sheet2").Select
     Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
     ActiveSheet.Paste

     'Move counter to next row
     LCopyToRow = LCopyToRow + 1

     'Go back to Sheet1 to continue searching
     Sheets("Sheet1").Select

  End If

  LSearchRow = LSearchRow + 1

 Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
 MsgBox "An error occurred."

End Sub

If you read your barcodes into Sheet2 ColA then you can use `VLOOKUP()` in ColB to read the names from your main sheet (but you'll need to switch the barcode column so it's to the left of the name column) — Tim Williams, Dec 10 '13 at 17:57
the person who would do the job of scanning is not good with coding , so I offered a help which I stuck with. what I need is more like this http://stackoverflow.com/questions/20482207/excel-vba-to-search-for-text-in-a-string-copy-paste-rows-to-new-sheet — Ali Abdullah, Dec 10 '13 at 18:01

score 0 · Accepted Answer · answered Dec 11 '13 at 06:49

See if this helps. I wasn't quite sure if you wanted just the name, or the whole row, copied over. This code just copies the name cell, but easily modified to do the entire row.

Sub SrchIDs()
    Dim rId As Range, celS As Range, celT As Range
    Dim wS As Worksheet, wT As Worksheet
    Dim sId As String

    Set wS = Worksheets("Sheet1")
    Set wT = Worksheets("Sheet2")
    Set celT = wT.Range("A2")

     Do

        sId = InputBox("Enter ID")
        If Len(sId) = 0 Then Exit Sub

        Set rId = wS.Range("H4") 'start of search area
        Set rId = wS.Range(rId, wS.Cells(wS.Rows.Count, rId.Column).End(xlUp)) 'rest of data

        Set celS = rId.Find(sId, , xlValues, xlWhole, , , False)

        If Not celS Is Nothing Then
            Set celS = Intersect(wS.Columns("F:F"), celS.EntireRow) 'extract name
            If Not IsEmpty(celT) Then 'find next empty cell in target sheet
                Set celT = wT.Cells(wT.Rows.Count, celT.Column).End(xlUp).Offset(1)
            End If
            celT.Value = celS.Value
        End If

    Loop Until Len(sId) = 0

End Sub

Excel macro to search a txt then copy 1cell and paste it in a new row in sheet 2

1 Answers1