I consider Auto as a setting to 'get' almost any shot, even those in terrible low light conditions. Cellphone and tablet cameras do the same. Based on a meter reading, and adjusted by what is, these days, surprisingly 'intelligent' firmware, the camera will take the picture. Auto mode today is like the former Kodak Instamatic or even the Brownie cameras...point and shoot. No thought/setup/settings required. It's akin to using nothing but a 50mm lens for all your picture taking. It works. It gets the shot. For the large majority of the general population, those cameras, and Auto mode today is quite sufficient.
But when you have some unusual lighting that the camera can't figure out, or you intentionally want a thin depth of field, for example, then it's time to take over some or all of the settings.
I recently went to an outdoor car show and simply set my camera on Av where I picked the aperture (f16 to get a 'good sized' DOF for the cars...from 10-20 feet away) and let the camera do the rest. Auto mode (for taking pictures of automobiles, right?) likely would have done just as well, but I wanted to 'guarantee' my DOF, so I took control of the aperture. Indoor, low light, no flash? I want to ensure that subject motion is stopped, I want the shutter speed in the range of 1/125 or faster. But as I frequently have to come to an 'acceptable compromise' (someone here came up with that phrase a couple of weeks ago, and I have come to use it a lot) where a little more ISO, a thinner DOF, and a bit slower shutter speed have to be set to get an acceptable exposure. The only way to do that is full manual.
But then, shooting 'cityscapes' in Chicago recently, I simply put the camera in Auto and fired away. I knew the camera would get completely acceptable shots just about every time. But when I wanted to capture an old fire-escape stairway in a dimly lit alley, I went to Av and selected f22 as I wanted to get most, if not all, the 10 story staircase in focus.
What it comes down to is letting the camera 'do its thing' when I want to, but when I recognize some special circumstances that I want to have control for something, I can make the needed settings and get the shot. But that doesn't mean my first shot in manual mode is 'right'. Often, I end up taking a couple of pictures looking at the histogram and making adjustment(s) to fix any over/under exposure and take another. There's times the metering is wrong, and resultant histogram, too... (did I remember to change metering modes?) and simply looking at the picture in the LCD and adjusting again is sometimes necessary.
As Overread said above: "What is important that you learn to use each mode you have, learn your aperture, ISO and shutter speed so that if you wish, you can shoot in full manual all the way to full auto and all inbetween."