Search specific column and then copy to new sheet

Hello again,

I need a way so that I can press a button and then a box will appear and allow me to type a search word. It will then scan a particular column if it contains that word and then it will copy the entire row and paste it into a new sheet.

It must be able to handle multiple search terms.

So: If I have multiple names like "John Smith" and then 20 rows down I have John Smith again and then another 10 times it has John Smith in that column, then it will copy the entire row in which john smith is within, (All of them) and paste them into a new sheet.

It should copy the entire row including the columns before, because the column where it is searching for will mostly likely not be column A.

Thank's again

~JSN

Give this a try. You did not specify which column to search in so this code assumes you want to search in the active cell column. This code uses filtering to copy all (exact) occurrences of the search term to Sheet2.

Please Login or Register  to view this content.

The code above DOES NOT create a Pivot Table . As I stated - it applies filtering, which is not the same.

Code below is revised per your last post to look only in column-D.

Please Login or Register  to view this content.

Also one last thing, to make a button which would reset the pivot table to show all.

You can turn the filter off via the menu. However, here is code which you can use to do this. Just create a button and assign this macro to the button.

Please Login or Register  to view this content.