I think Stormynature said it much better than me.
By telling his units build him a city, I was thinking in the lines of something like Age of Empires where there is an option for the units to make a city. Not placing the buildings yourself.
One idea for the ultimate strategy game maybe one that lets the player start very low level. Eg worker/builder. And slowly gives the player a higher level of management with new gameplay mechanics and things to do (Stormynature's idea). In a sense that's what Spore does. It starts low level and by the end of the game you explore planets. In such a case automation would be necessary to take care of the lower level stuff.
That way the player can concentrate on what he is doing currently.
Then yes the player wouldn't really care for the 23rd city so much hehe. He would be thinking about the countries needs and/or something more high level. In the same time this feels like 3/4 games into one. A city manager, A country manager and so on. Most cases these type of games are different. Eg Age of Empires is real time while something like Civilization 5 is turn based.
That alone makes a large difference in how things play out. Making such a complex game will not be easy hehe. Maybe bringing new situations can be enough. Maybe you are right. I like your idea, maybe one day you can build such a game and see it it works If the level of management is the same throughout the game experience, automation can be a great tool. The fun level would depend on the implementation. Then starting something (eg new city) for the 10th/11th time would not be as repetitive as you can use an already created 'procedure' to speed start things.
How much automation does a game allow ? I think that's the key thing here. Should a game let the player be able to save every action they do and later just apply it to different units? Or only certain parts.....It will be interesting how strategy games evolve over time hehe