If CVTs can be made durable enough to last 200K+ mi, without being totally numb, I could live with them, but yes, I would miss a transmission that shifts.
A manual is the most fun, for sure, and I find that when I have the opportunity to drive one, it is really rewarding.
A conventional automatic is obviously less fun, but still can be a little fun. My truck firms up the shifts when you punch it, and will hold a gear past 5000 RPM if you have your foot in the gas. Though it lacks the truely connected feel of a manual, hearing/feeling it go through the gears is good for the senses. It might not be the quickest vehicle out there, but it accelerates with purpose.
Some of the CVTs I have driven just feel totally disconnected. The engine revs, but the car doesn't go, at least not the way I expected. They are the most numb of all transmissions, which many people like, but I personally don't. Add in reliability concerns, and a total lack of CVTs in any application that is remotely heavy duty/intended to work, and they just loose all appeal to me.