The goal is always to get a 'static' mix that's 90% there, and then use automation to bring it to life. You can use it to create the illusion of dynamics, contrast, excitement. On drums, go through and automate any fills or hits that aren't coming through. I always automate (ride) the overhead track by hand to bring out all the crashes. Automate bass when compression isn't getting you all the way there. Vocals are obvious, you gotta make sure every line is audible and intelligible. But you can do so much with it... don't shy away. automation is one of the things that set apart the 'men from the boys' IMO, and with a DAW you have no limits. Automate EQs, effects, etc.
One big tip I have for automation is to try and do it without looking at the waveform - especially vocals. It's easy to go in there with your mouse and draw a ton of stuff and actually automate the life OUT of it. If you're struggling with automation and you have a control surface, turn the monitor off and ride some faders. You'll rely more on your ears and your instinct and end up with a more exciting mix, pretty much guaranteed.