First I just want to give some advice:
If you were my intern I would be happy if you simply asked my advice. I would want you to show what you’ve attempted and could tell me why pieces of it worked and why pieces of it didn’t work. I wouldn’t necessarily expect you to get something 100% automated all on your own. The key is that you tried and can learn from your mistakes.
Communication is another very important aspect. I’d much rather have a junior developer ask me 10000 questions instead of developing on their own with 0 questions and then delivering a sub-par product requiring complete re-work.
NOW, onto the answering of the actual question.
I’m struggling to understand exactly what you’re trying to achieve. It is very do-able to save excel and dynamically change the number of sheets. What exactly do you mean with the ‘…from a drop down list in an external application’ portion? Does the dropdown contain a number and that number corresponds to the amount of sheets? Screenshots may be helpful (only do this if your company allows though) - please do not share any confidential or personally identifying information