I think at some point in the future, driving a manual will be pointless. There are some pretty exciting things going on right now in automatic transmission technology (at least with the Japanese and the Germans; the US OEMs are hopeless). Mercedes has been steadily improving on the 7-speed automatic they rolled out in 2003, and ZF (a German tranny OEM) has introduced an 8-speed that will go into BMWs. Toyota are also shipping 8-speed transmissions on certain Lexus models. These are not your father's automatics: they offer much better performance, faster shifting and far better fuel economy than the old 4- or 5-speed models. Right now, these tend to be on high-end cars, but I'd expect that the technology will trickle down to more affordable cars eventually.
For me, though, the really exciting development work is in
double-clutch transmissions (DCTs), which are best thought of as
automated manual transmissions, i.e., manuals with no clutch pedal. They employ separate clutches for odd and even gears, and thus shifts are instantaneous with no loss of power during shifting, unlike with conventional manuals. Up until recently, VW and Audi have been the main proponents of these, but BMW and others are getting into the act.
As for me, I've been driving only manuals since 1981, but I think I could be persuaded to switch to a DCT if it becomes available on a model I care about.