I think you're right: you're headed down the scripted automation path.
What I'd recommend for those with little or no previous Excel programming programming would be to try to use Excel macro recording facility to generate your basic code. The macro recorder literally records what you do (open a file, copy a range, paste it somewhere, etc) and creates the program code that will perform those steps on your command.
You can then examine the generated code to learn what's required, in terms of program code, to do what you want to do.
Your IT group may or may not have the background to understand what you want to do, and be able to assist with the programming. If you get stuck, let us know and we Excel programmers around here will do our best, I'm sure, to help.