Changing one sheet of a template output and writing it to multiple files

[Alayna]

I have a list of thousands of employees, and I'd like to be able to separate their names into separate files by business unit description and division. So that a file name will say "PU_D0130", and 200 business units will result in 200 different files. The issue is that I have a template, and I only want to paste into one sheet of the template, then write the entire workbook to a different file, and repeat that process until I have separate files with the business units if that makes sense.

I haven't gotten to batch micros yet in the curriculum, but I think my question is similar to this one, but I don't want to create exact copies of the same sheet, just paste different list of names for one sheet in a copy of the template. I've also read through this article, but it seems that it doesn't work well when there's already pre-existing template form the comments. Any help is appreciated

[Qiu · Accepted solution]

@Alayna 

Really worked on this one, macro in macro.

So if I understand your intention correctly, you want to repeatly use one template and output multiple files with dynamic names.

Please adjust the detailed file paths in workflow, macro 1 and macro 2.

[Alayna]

@Qiu Thank you so much for the help!!! I'm trying to replicate this with my larger spreadsheet, but could you explain what the tile function does and why we filter out Tile=1 first in the macros? Really appreciate the workflow 🙂

[Qiu]

@Alayna 

First macro is a batch macro, so tile function will assign unique ID for unique combination of business unit description and division.

Then each time will pass one Tile Number to the macro. The macro will then feed the required File Name, which has to be dynamic for the second macro.

Seccond macro will first create a copy of your templete then output the filtered result with preserving formating option.

If you allow, I am thinking to submit this idea as a weekly challenge.

[Alayna]

@Qiu Thank you for the help and explanation! This has really helped me out, and you can definitely submit this idea for the weekly challenge. 

[Qiu]

@Alayna 

Glad to help and thank you for the aceept mark.

I will try and submit it as a weekly challenge.