Is there any sort of economic limit to automation?

The argument in favor of automation I’ve generally heard is that it opens up opportunities for people to pursue other lines of work. The majority of the population was farmers not so long ago, but now we have careers that people back in the day would have never imagined, like marketing manager or something

What I’m wondering is, does economics consider there to be some “limit” to this? Like is it possible to reach a level of automation that it starts harming the economy more than helping it?

Like let’s say the average needs of the average person can be mostly automated. Their food, their housing, transportation, etc. does the price fall so low that they can afford it with minimal work? Does it become paradoxically impossible to afford cause there’s no work to be had?