I'll offer 3 very simple reasons for having more megapixels.
(1) Useful for very large prints. You will see more detail close up.
(2) Effectively improves the resolution of all one's lenses by at least some degree, depending on the inherent quality of the lenses, of course. The higher the quality of the lens, the more pronounced the improvement will be.
(3) Provides the flexibility to create one's own cropped format, thus effectively getting a longer reach with any lens.
One of the advantages of the D800E that appeals to me, is that it performs like a cropped-format D7000 when used in DX mode, and also provides a faster frame rate because of the smaller file sizes used. I thus get the sense that I have two cameras for the price of one.
It's interesting that Nikon have now done something similar with their recently announced 24mp D7100. They've introduced another 1.3x cropped format within the wider DX viewfinder which allows one to see what's outside the smaller format, as in a rangefinder camera.
The additional cropped format provides a 15.4mp image with an effective focal length of 2x the 35mm equivalent, and again a faster frame rate as a result of the reduced file size.
Looks like I'll be carrying two cameras again. The D7100 seems a worthy upgrade to the D7000, and its lack of an AA filter seems very sensible.