For me it's been Provia 400X as the best "allround" film, you can push or pull it and for an ASA400 film it's got very fine grain indeed - I shoot MF and ASA400 lets me shoot most of shots handheld without worring. Its got fairly good DR for a E6 film, plus capable of shooting people when you need to and very good reciprocity properties.
Wind's Painting by tsiklonaut, on Flickr
Frozen landscape by tsiklonaut, on Flickr
Provia 100F was my preferred film for many years, until I really got too much into 400X. Probably because I use a lot of polarizer filter (-2 fstops), so ASA100 can limit my handheld shots. But I love Prova 100F from every angle, so maybe I have to re-visit 100F and just use more tripod...
Obviously like many, Velvia 100 I like for landscapes, sunsets sunrises, dawns (V100 has good reciprocity properties unlike Velvia 50), Velvia 50 for daylight landscapes - it has the most "syrreal" colours that can benefit those rather dull light conditioned scenes without going overboard.
Tanjung Aan beach by tsiklonaut, on Flickr
And for very artistic shots: nothing beats the Kodak (EIR) Aerochrome colour infrared slide
Motorcycling dream (drum scan) by tsiklonaut, on Flickr
Sandra & Sentos by tsiklonaut, on Flickr