I need to make a macro that will go through a column of data and looks for a
specific word, when it finds that word it should copy the data from the cell
directly to the right of it and make a list.

Example (2 columns the word "employee" is in one and the name is in another)

Start with this:

Employee Name1
random stuff taking up lots of columns along the row
random stuff taking up lots of columns along the row
random stuff taking up lots of columns along the row

Employee Name2
random stuff taking up lots of columns along the row

Employee Name3
random stuff taking up lots of columns along the row
random stuff taking up lots of columns along the row

Employee Name4
random stuff taking up lots of columns along the row

Employee Name5

Employee Name6

and I want just this:

Name1
Name2
Name3
Name4
Name5
Name6

There is a varying number of rows between each employee so I don't think a
formula would work. I want the macro to look for every instance of the word
"employee" and give me just a list of names on a new sheet. Is this
possible? Could this be done with a formula instead?