Macro to search a string, then search above for another one and copy the rows between them

Good night,
Im having a really hard time trying to figure out how to make a Macro to Search for a Substring then search the rows above for a different one and finally select the cells until a third criteria is met, after the selection is made copy this to a different sheet based on another string that must be searched.

Lets say this is the info from the Data Sheet all the information for every row is on the same cell in column A, the number of rows between the "----" and the number of total rows are not always the same, sometimes there are blank rows in the data. For the Example we'll use 3 aditional sheets "P", "O" and "I".

----
P11
111
11x
111
----
O11
111
11z
----
I11

11x
----

What I'm looking for is a way to search for "X", then search the cells above for "----" and made a selection til the next "----" cell, when this is made, search inside the selection for "P", "O" or "I", and copy the selection to the sheet with the same name, then repeat the process until all the rows are done. Using the info above the macro should encounter the first "X" on the 4th row then search above and select from the 1st to 6th row and copy this selection to Sheet "P".

Thanks for your time.

Hi DarKDjinni, try it

Please Login or Register  to view this content.

Hi

Try this

Please Login or Register  to view this content.

The example data doesn't seem to have anthing that will go into sheet O. Is this correct?

rylo

Yet one more...

Please Login or Register  to view this content.

Originally Posted by DarKDjinni

So my question is should I flag this as Solved and start a new thread or post my new questions here with a new sample sheet ?

What do you really need?

Well a Code to do what I asked for in the beginning, copy a range of rows based on multiple criterias and you provide me with that but I need it to work with different data, I thought that I will be able to do a few tweaks to your codes to work with my real data but I couldn't...

If anybody stills wants to give me a hand, this time the sample sheet will have the data as much closer to the real that I could possibly upload, on the sample sheet I replaced every letter with an "X" and the numbers with "1", and put the criterias on bold and a green Font.

Then the code should search for this substrings:
"Message Type: R" or "Status: R"
Then search above for the line "-----" , select until the next line "-----", when this is done search inside the selection for this substrings:
"Number:999xx" (the "xx" could be any number from 05 to 95) or "Number: 999xx" (With 2 spaces between the ":" and the numbers) and copy the selection to the sheet with the same name as the number (999xx) it found, then repeat the process unitl all the rows are done.

On the sample sheet are 4 selections that met this criteria for every sheet from 99905 to 99930
and sheets 99940, 99950, 99960, 99970, 99980 and 99990

Thank you so much guys

Hi

You say you want to search for message type r or status r, but you have highlighted mesage type a and status a in green font in your example file to signify data to transfer. Exactly what data should be copied across?

rylo

Just use the ones with an "R" as criteria, the other are there just to show that there are different Status.

The "Status A" and "Message Type A" shouldn't be copied and Im not really sure why I highlighted those.

Thanks

Hi

OK, see how this goes.

Please Login or Register  to view this content.

rylo

I tested your code with my raw data and it works great, its amazing.

Thank you,