Search, copy and paste macro.

**Shin278** · 08-08-2011, 07:43 AM

Hi, I'm rather new to the whole making macros for excel thing and have got rather stuck on trying to create a macro.

What I am trying to do is create a macro which will search for a word or number which is entered into a dialog box, with the entered word or number I would like for multiple worksheets to be searched and for each instance that the searched item appears the row it is present in to be copied and then finally pasted to a sheet which I will define as "Search" for example.

I hope what I am trying to do comes across clearly enough.

Any help or advice on what I can do will be much appreciated.

Thanks.

**JBeaucaire** · 08-08-2011, 08:48 AM

Assuming:

1) You will place a search button on your sheet called "Search" to run this macro
2) You want the search sheet to only list the rows of the most recent search
3) You want ALL sheet searched except the "Search" sheet
4) You want ALL cells searched on each sheet
5) You want to see the entire row(s)
6) That sometimes the search string is a "whole cell" value and other times it is a "partial cell" value

Try this:

Please Login or Register  to view this content.

**Shin278** · 08-08-2011, 09:38 AM

Thank you very much JBeaucaire, the solution you provided worked great for what I needed to do. Sorry about the poorly articulated explanation of what I needed in the original post.

Thanks.

**alwaysgo** · 08-16-2011, 07:42 AM

JBeaucaire, I was looking for a similar solution and your code was very helpful. Since I am novice in VBA, as well, could you please help to modify it slightly to meet my needs. In the nutshell, that is what I am looking for:
~ Once I enter the search criterion and hit the button, I would like also to search through multiple worksheets, but only at the specific column on each worksheet, for example, column C.
~ In my case, the value I am searching for will be a unique one, so as soon as I found it, I would like to go to that particular worksheet where this value was found. For example, I started from my "Search" worksheet and searched through dozens of worksheets, once the value is found, it is displayed on that particular worksheet "as is" and the execution of code ends.

I would appreciate your help. I am running Excel 2007.