Macro to create new file each month

[mikekosmo]

Every morning, I have a macro automatically run that updates a few history files. Each history file is large, so they must be split up by month. I'm spoiled by the Application.OnTime function everyday and don't want to have to make new history files each month. Is there a way to create new history files on the first of every month? The file would be basic, just the column headers and the "...Summary 2020-MM.xlsx" filename.

[JLGWhiz]

Is your computer on 24/7? If not, and the first day of the month falls on a non-workday, then any automatic generation of a new file would be bypassed based on the date. But you could create a template and just open the template and do a saveas to create your new file.

[mikekosmo]

I could leave the computer on 24/7, or at least keep it on around the new month. You did spark an idea. I could use Task Scheduler to open templates on the first of every month at a set time. Then use Application.OnTime function within these templates to save them with the new month in the filename and saved in the proper folders.

[juddaaaa]

This would check to see if the file exists based on the example ....Summary 2020-MM.xlsx in your chosen folder every time you open the file.

Please Login or Register  to view this content.

Because it's using today's date to create the filename, the first time it runs in a new month it will create the new file.

For the rest of the month, once the file has been created it does nothing.

[JLGWhiz]

I think @juddaaaa has the best solution that does not require any exotic coding or special attention. Just a locgical check of the file name against the directory upon opening your workbook. It compensates for any weekends or holidays that might fall on the first of the month.

[mikekosmo]

JUDDAAAA! I love what you did. The template macro runs dormant if there's already a file with that name. I'll have to give it a shot tomorrow.