Find Text Within A Row, Then Copy Information Below That Cell To Second Worksheet

[kstoneman]

I’ve recently taken over an Excel workbook that uses VBA to find, copy, and paste information between two workbooks. I’m relatively new to Excel VBA coding, so I’m looking for any help that will reduce the vast number of lines, over 300, of redundant coding. I will be unable to provide the actual coding as it includes sensitive information and would take a lot of time to make the coding genetic. Here is what I want to accomplish:
· Find each instance of text within a row range
· Once I find a cell with text, I want to copy the cell and a 5x5 block of information, which is two rows below that cell, to another workbook
· The copied information needs to be appended to the last used row in the second worksheet

I’ve searched the web for ideas on how to accomplish this. I can find snippets of coding that can do some parts, but I don’t know how to consolidate and organize the coding into a single process.
The attached file is a very raw example of the type of data I’m using. The three blocks of values on the source tab need to be pasted into the destination worksheet. Currently, in the destination tab is the format/result required.

Any help is greatly appreciated.

[arnelgp]

here you test this.

[Aussiebear]

@arnelgp, In your command_button module you use the following line

"Dim rng As Range, last_row As Long, arr, r As Range"

What does the arr represent?

[arnelgp]

@arnelgp, In your command_button module you use the following line

"Dim rng As Range, last_row As Long, arr, r As Range"

What does the arr represent?

it was just a preparation, so the arr and i think r is orphaned.
at first i am undesided if i will go using array but decided not to.
so that can be safely erase.

[Aussiebear]

Thank you

[p45cal]

cross posted
https://chandoo.org/forum/threads/fi...rksheet.48454/
https://www.excelforum.com/excel-pro...worksheet.html

[Aussiebear]

@kstoneman. Is this true?