HELP copying multiple ranges per row based on cell criteria using a Loop

Hello,

I have been struggling to find a means of identifying rows of data based on their cell value in column "J" (RequestID), and then copying certain ranges in the target rows to copy to another sheet.

In the attached example, I am trying to identify each row in Sheet1 that has an REQ number in column "J".
If column "J" has an REQ number, then I want to copy that row's cells in columns A:E and H:I to Sheet2 and then loop through the rest of the rows that have an REQ number and do the same.

By the end of the macro, Sheet2 would show a list of cell data from columns A:D and H:I for each row that had an REQ number in column "J" on Sheet1 and have stripped out formatting so it is just text.

I have put an example of what the final product would hopefully look like on Sheet3.

Thanks so much in advance for your help!

AlphaFrog - Thanks for the quick reply! I have certainly learned by looking at your code - the range line in particular is what I was struggling with the most. Would you mind sharing what the "- 1" does after the ".Rows.Count" parts of the range definition?