table search and copy to sheet 2

booomer

New Member
Joined
Nov 14, 2002
Messages
1
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,516
Messages
5,596,617
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top