It depends on the movie and the transfer.
For example on many animated movies, the improvement can be subtle, and on others (like Monsters Inc.), the detail is amazing in HD.
Stuff like Battlestar Galactica in HD can be disappointing because it's not transferred well (IMO).
Many of the upconverting DVD players are fantastic. If you're migrating from an upconverting DVD player to HD, the improvments are subtle but real in many cases.
I've had HD and HD cable as a source for almost two years, and there are a few things that continue to wow me. Things like:
The Tonight Show
David Letterman
Shuttle Launches (wow!)
Imax movies and the like
If you really want to be wowed, get yourself an HD camcorder. I've showed many people my HD setup, and while some are more impressed than others, as soon as I hook up the HD camcorder they are floored.
GAD