Need help with Macro sending data to new sheet

I have this macro that finds data searching rows. I have to hit next for it to find the next set if same data I would like the ability for it to paste all the data it finds on the next sheet

Currently I have to stop the search to copy and paste data and then start all over again. Plus it is VERY time consuming I've tried other forums and have lots of views but no help so hopefully someone here can figure out what I need Thank you

Instructions:

When you open the file you will see under column C the numbers 2 10 2 in rows C:2 C:3 C:4. I am looking for all the number sequences down column C that have the 2 10 2 in them

Click on the button labeled "Find Patterns"
That brings up a box that says select range(s) you will high lite C:2 to E:2 and drag down to C:4 E:r and then press "ok"

This will begin the search. When it finds the answers it will show you a location that the next set of numbers ( 2 10 2) are in $C$17:$E$19
click on "ok" this brings you to that location. This will be one location of many. There are well over 100,000 rows I will be searching

Now, for what I want I need help with. I want to be able for the Macro to copy and paste all the locations it finds and place them on the next sheet with 10 rows above and 10 rows below (info from B:J)

If this can not be done easily then I would like the ability to pause the search and copy the location to another sheet manually and then start where it left off. Presently every-time I go to copy and paste, I have to stop it and start all over again. Ideal would be to copy and paste to the next sheet which will save an enormous amount of time


http://www.mediafire.com/download/u5...er_Search.xlsm

Hi, Vortex69,

you should attach the workboiok here on EF instead of another provider where it may get removed after some tiome.

Try looking up FindNext for your purpose in the VBA Help or here on EF to continue the search.

Ciao,
Holger

Attach a sample workbook.
Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I can not make the file small enough to upload and it still make sense or at least I do not think I can, so what I will do is upload a pic and the Macro and then a spreadsheet with a lot of data missing

I can not get it under 1.56MB

Here is the code

Please Login or Register  to view this content.

Number Search.jpg