My V6 is an auto; to be frank, I'm a car enthusiast and a Autopian when it comes to care (at 9500 miles my car still looks brand new), but my life doesnt allow much other than a commute to work and back during the weekdays (and its only 7 miles). I also live in Boston, effectively, so I love my 5-gear tiptronic.
I think if you get a "fast" sport tuned new car, I think you will find that its tiptronic mode will give you what you want *unless* you plan to autocross. My tiptronic works fantastic for planned "here I'm going to gun it" and for "ok, lets downshift and keep the revs high ready for gunning it and merging with traffic" or even "keep it in first gear to impress my friends with torque". I can do almost anything a manual driver can do short of harming the car (like I can't downshift from 4 to 1, but would a manual driver be doing the right thing??).
I can't see myself autocrossing very well, obviously since I wouldn't know perfectly what gear I'm in; but for drag, I think an auto has the upper hand in consistency. The computer (again assuming a decent new automatic car tuned for sport shifts) can't mess up the shifting!
Now, nevertheless, here's my story if I didn't need to worry about money: my daily driver, no matter how fun (my car is a blast right now! but still, auto it is.), would likely be an automatic. However, if I were to buy a second car, as a fun car like a miata, cooper, or other sports car, I would very likely make that a manual.
Finally....
research if the car you are interested in is known to have "issues" with its manual transmissions (or auto for that matter!). In my case, 05 Nissan Altima's have known issues with their manuals, but their automatics rule!!!
edit: rewrite some sentences as I wrote this while I was on the phone...
