Can I take it that you're only doing this job once? If so, it will almost
certainly be quicker to do it by hand than by writing code.

One facility that probably will help is auto-filter. If you have a sheet
that you need to split up, you can auto-filter for the first  choice, then
create a new sheet  for that selection and copy the visible rows across.
Then filter for the next choice and repeat.

The nice thing about auto-filter is that the little drop-down shows you all
unique values for the column, so you know in advance how many you're dealing
with. You can also spot misspellings from this drop-down with ease, then
filter on the bad one and change it to good, then filter on the proper one
and deal with them all, together.

I'm automating the process to be used once a
month or so. I coded it to use autofilter the first  time around. I
guess I can use it again now on each sheet  to separate  them out
again. For the other scenario  where the same name  appears on a few
sheets in a row  - Are there any suggestions on how to code this
efficiently? I need to combine them to one sheet.

No I don't really have any suggestions for when the same name  appears on a
few sheets  in a row. The problem is that it is probably too late to do a
lot - it would have been nice to identify them as the same name before
sending them to the different sheets.

My feeling is that you'd be better off keeping everything in the one place
until the analysis is finished, then split it up based on that analysis.