Excel VBA: search sheet for a partial string, then collapses and pastes into another sheet


I'm new at VBA and I need a little help writing a code to perform a specific function.

I need to be able to search column "B" for a partial string, and output the row into another sheet.

For example, I need to pull all the rows that have the word "MOTOR" as part of the string in column "B" and paste those rows in "sheet 2" The sting in column "B" could have a variety of strings such as "TEFCMOTOR286B2" or "MOTORSLIDEBASE286B2" and I need to copy both of the rows with the previous strings and make them into a list on "sheet 2" The number of Rows that need to be copied will vary and sometimes there wont be any with the partial string "MOTOR" at all.

Let me know if you need more info.

This is a perfect job for the Macro Recorder

  1. switch on the Recorder
  2. place an AutoFilter on the original data
  3. Using AutoFilter pick Text Filters > Contains...
  4. copy the visible rows and paste to the other sheet
  5. switch off the Recorder