Excel Macros Create Monthly Workbooks Entire Year

Excel Macros Create Monthly Workbooks Entire Year

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

click button to run macro

Macro Messages

When the macro starts, a message appears, asking you to enter a 4-digit year number.

enter year number for workbooks

Another message asks if you want the month’s day to be in ordinal format, like 1st, 2nd, 3rd.

Click Yes or No.

choose number format for daily worksheets

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.

worksheet for each day of the month

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.

automatically add monthly sheets

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

excelmacrosmakeworkbooks01a

_______________

One thought on “Excel Macros Create Monthly Workbooks Entire Year”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.