I just bought a TSX a month ago, and I got the 6-speed manual transmission.
If you check the other dedicated TSX forums (h-a.net doesn't have enough TSX chat for me), you'll see that most either have the manual transmission, or they have the automatic and regretting it. The only ones who went with automatic are the ones who have wives that can't drive stick ;-)
It really depends on your preference. The reviews consider the TSX to be a "sports sedan"...so you need a manual to be sporty ;-). Some consider the TSX to be "entry luxury", so you might want an auto. So do you want a sporty car or a luxury car?
The auto has slightly better fuel economy, but I believe there is some dislikes about it (downshifting automatically on mountains when the car detects that it's at a steep incline), and I think some have complained that there's a slight lag (beyond that caused by the drive-by-wire system).
The TSX's manual is so smooth. Almost everyone who has tried it has noticed...it's a car you'd want to learn how to drive a stick on.
I hadn't driven a manual for 10 years before I bought the TSX...I'm still getting used to downshifting, double clutching, etc. But the thing I like the most about having a manual is that I can stay at the lower gears longer, which lets me keep the TSX in the higher rpm's where it makes most of it's power.
The only complaint I have about having a manual is that I can no longer eat/drink while driving, and I can't drive if I'm extremely sleepy (so I guess you can consider those plusses)