It seems that manual transmissions make a more notable improvement in lower powered vehicles. This seems to be true for fuel economy, acceleration and the "fun to drive" factor.
Edit: Thanks to the guys who pointed out that automatics draw a percentage of power rather than a set amount. I did not know that!