Macro Formula needed to find a value and remember the row of the value, repeat

Hello all! I'm new to the forum and am a total nube when I comes to excel. I am trying to create a macro that is more than recording a few steps the macro recorder therefore I need some assistance writing the code. The below process currently take me a few hours to accomplish manually.

1. First off is I pull an extract from another data base and copy it into excel. It is placed in Sheet 1 column A and has around 5000 rows.

2. What I then do is have to parse the data into different sheets around 30 of them. This is where I need the macro

3. The amount of data in each of the extracts is different therefore I can't set a specific row to row of data to copied and pasted in the macro.

4. I need it to perform a search in Sheet 1 for a specific value in one of the rows, say 100D, then have the macro remember the row 100D was in

5. Then perform another search for another value, say 200D, then have it remember the row it is in.

6. Then have it copy the selection from row 100D was in through to the row the 200D minus 1 row therefore capturing all the data for the 100D portion of the extract.

7. Then the data pasted is to the desired sheet2 (100D data)

8. Then have it go back to Sheet 1 and delete the Selection that was pasted into Sheet2

9. I will need to complete this transaction for 30 sheets of data. The next would be the same but searching for 200D, and then 300D and so on.

I hope this makes sense and if it doesn’t please let me know.

I have posted a file to at the following link:
http://www.box.net/shared/35iosg3grvr9lqdqek19

I hope this can be accomplished using a single macro and know that any direction is greatly appreciated.

66Deuce,


Welcome to the forum.

When I looked at your workbook I did not see as your example "100D" in the main sheet. I did see "P70100D". Is this what you are looking for?