Your first requirement is reasonably clear, although I'd still ask what do you mean by 'delete and transfer'. Does it mean to cut and paste? Or did you mean that you want to delete all rows that contain Received and Pending.

What's the second requirement? You mean you want to move all rows that have any one of the words "CA","US","ERROR","#VALUE!","XXX","YYY","VVV" to another worksheet in the same workbook?