Tried Power Automate. First time I’ve used it and tried to record the process with that but it only seems to record the clicking around on sheets that I did. Wouldn’t record me running macros?
Anyway, I did the suggestion of recording a macro and going through the process to create one one file. I’ve got that working and I’ve tweaked the code to get it to do everything I need for one iteration. I just need to get it to work for however many I need it to run.
Basically each month were using this file to create a price for an individual client. This takes up a ridiculous amount of my teams time and the basic process is put the client identifying number in, put the required date in, stick the client managers name in and stick your name in. You then run a macro that goes to a data warehouse and pulls in lots of information about that client. You then run another macro that goes and runs some SAS reports and pulls that data into the file. Once that’s done you save the file as Client Name Month Number.xlsm, then move onto the next one. That whole process completely hogs your excel, so you can’t do anything else while it’s running, and each person in my team could have 40-50 of these to do in a couple of days. It’s a ridiculously inefficient use of time. In an ideal world we do this on a virtual machine or using some other fancy tech but in the immediate now it has to be done on our laptops. It’s the same every month, the same 4 parameters they need to enter, the same 2 macros they need to run. My short term solution is this batch process that says use this little table with the 4 parameters and 50 different clients you have to run, and do it at 5pm so they’re waiting ready in the morning