Happy December! We’ve almost made it to the end of this year, and you’re probably getting reading for next year’s tasks and projects. If you need to set up monthly workbooks for next year, or automatically add sheets to existing workbooks, these Excel macros can help you!
Create Monthly Workbooks
At one company I’ve worked with, some of their data was organized in a separate folder for each year, with an Excel workbook for each month’s data.
Was that an ideal setup? Probably not. But that’s how things had been done for years, and it wasn’t worth changing!
Anyway, there’s good news, if you’re working with a system like that. There’s an Excel macro that can make your job easier, when it’s time to create a year’s workbooks.
And there’s another macro that will automatically add a monthly sheet to an Excel workbook.
Macro Creates Workbooks
Instead of creating all the monthly Excel files, you can click a button, and a macro quickly creates all 12 workbooks.
Roger Govier used this macro to help his consulting clients at the end of the year, and it’s a real time saver! So, he generously shared it with us, in case you need to do this too.
- Download Roger’s sample file, then unzip it, and enable the macros.
- Read the macro notes, then click the button to create the workbooks
Macro Messages
When the macro starts, a message appears, asking you to enter a 4-digit year number.
Another message asks if you want the month’s day to be in ordinal format, like 1st, 2nd, 3rd.
Click Yes or No.
After the workbooks have been created, a final message appears, telling you where the monthly workbooks are located.
If you open one of the monthly files, you’ll see that it has a sheet for each day of that month, with the numbers in the format that you selected.
Macro Adds Month Sheet
There’s another macro on my Contextures site, and it automatically inserts a new sheet, the first time you open the workbook each month.
Here’s the November sheet tab, with the 2-digit month number and short month name.
Video: Automatically Add Monthly Sheets
In this video, see how to automatically add a new worksheet with the month name, when an Excel file opens at the start of the month.
Get the Macro Workbooks
For the Excel files, and to see the written steps, you can go to the Create Workbooks and Worksheets page on my Contextures site.
All three workbooks are listed there, with more details on the macros. The zipped files contain macros, so be sure to unblock them in Windows Explorer, before you open them.
After you open the files, if you want to test the macros, enable macros, when the security message appears.
__________________
Excel Macros Create Monthly Workbooks Entire Year
_______________