Dallas here from the Shopify Social Care Team. I can totally help you with this.
I would list out all the steps for you but there is this great guide from our Help Center that goes into more detail when it comes to hiding sold out items. That guide also gives the steps for a few different situations too. Our Knowledge Management team does a great job at showcasing all the guides in a very easy to follow format, but if you have any more questions or if you need more assistance please let me know!
I'm happy to help.
If you're using a Shopify theme I might even be able to get out themes team to customize this for you for free. May I ask what theme you're using?
Automated collections are only able to be made when you first make a collection. So if you have already made a manual collection you'd have to remake that collection but as an automated one.
This link here has more information on how to go about setting automated collections up.
Can you try that out and let me know how it goes?