table search and copy to sheet 2

[booomer]

I have a 12 column 300 row table.
I want to search a particular column in each row for a specific word.
If a word is found, I want to copy the entire row to another sheet.
I want to check the entire 300 rows.
Can a macro be created to do this?

Thanks in advace for you help.

 

[Asala42]

Hey there,

I know a macro could do this but it looks like you might just need an Auto-Filter if this is a one-shot deal.

DataFilterAuto-Filter should give you drop-down arrows on your column headings. Select the word you're looking and that will compress the data to reconds only having that word. Then you're just a copypaste away from getting that data to sheet2.

HTH
Adam

 

[Tom Urtis]

Hi booomer --

You left out a few details so I ad lib'd a little on this Friday afternoon.

(1)
You did not say if you want to search every cell in the table, or just one column. I assume you meant one column. In the macro below, I assumed column A, so modify that if your search is for one column but not column A. If it is the entire table, then repost for assistance on modifying that if you need help.

(2)
You said you have a 300-row table, and if it is always exactly 300 rows we can modify that range reference too...the code below will search for all of column A. If your table ends at row 300 and you have data below that which you do not want included in the search, please repost for help on modifying that too. I took my chances with assuming you might have a dynamic range, and we had to start somewhere.

(3)
I assumed you want the copied row to appear on another sheet, in the next available row, instead of overriding an existing previously copied row.

(4)
I named your "copy to" sheet as Sheet2, so modify that if you need to.

A lot of qualifiers for a simple macro! Sorry, but there are several ways to do this based on whatever combinations of circumstances you have. Anyway, see if this works for you; it did for me, tested on XP:

''''''''''''''''''''''''''''''''''''''''''''

Sub WordCopy()
Dim myWord As String
myWord = InputBox("Please enter the magic word:", "What word do you want to find?")
On Error GoTo e
If myWord = "" Then Exit Sub
Columns("A:A").Find(What:=myWord, LookAt:=xlWhole).EntireRow.Copy _
Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
MsgBox myWord & " was found and has been copied.", 64, "Spread the word."
Exit Sub
e:
MsgBox myWord & " was not found.", 64, "Mum's the word."
End Sub

''''''''''''''''''''''''''''''''''''''''''''