Add Month Sheets Automatically in Excel

Set up a Master sheet in your workbook, and add month sheets automatically, based on that Master sheet. The new sheets will be named for the month and year, in yyyy_mm format.

Add Month Sheets Automatically

This video shows the steps for setting up the master sheet and macros, to add month sheets automatically in a workbook. Written details are below the video.

Create a Master Sheet

The first step is to set up a Master sheet in your workbook. In my sample file, the sheet is named wkst_Master.

It has a named Excel table, tblSales, starting in cell A1.

Master Sheet Freeze Pane

To save time in the new sheets, be sure that the Master sheet is set up exactly the way you want it.

In my sample file, there is a Freeze Pane setting, with the first row frozen at the top.

This keeps the table’s heading row visible, when you scroll down on the sheet.

Master Sheet Selected Cell

Another setting that I made on the Master sheet was to select cell A1. It’s a minor thing, but it saves you the time of selecting that cell on each new sheet that’s created.

Macro to Add Month Sheets

In the sample file, there is a macro to add month sheets automatically, as needed.

This macro, named AddMonthWkst, is stored on a regular code module, named modSheets, and does the following steps:

sets the sheet which will be used as the Master
calculates the current year and month name, in yyyy_mm format
checks for a sheet with that year/month name
if not found, it creates a new sheet, based on Wkst_Master
names new sheet with current year and month

Macro Code to Add Month Sheets

Here is the code for the AddMonthWkst macro.

NOTE: You can change the master sheet name, and the date formatting, to match what you need in your workbook.

Sub AddMonthWkst()
Dim ws As Worksheet
Dim wsM As Worksheet
Dim strName As String
Dim bCheck As Boolean

On Error Resume Next
Set wsM = Sheets("Wkst_Master")
strName = Format(Date, "yyyy_mm")
bCheck = Len(Sheets(strName).Name) > 0

If bCheck = False Then
'add new sheet after Instructions
    wsM.Copy After:=Sheets(1)
    ActiveSheet.Name = strName
End If

Set wsM = Nothing
End Sub

Workbook Open Code

To make that macro run automatically when the workbook opens, there is code in the ThisWorkbook module too.

The code is in the Workbook_Open event, and all it does is run the AddMonthWkst macro.

Private Sub Workbook_Open()
    AddMonthWkst
End Sub

Test the Month Sheets Macro

After you add the Workbook_Open code and the AddMonthWkst macro code to your workbook, close the workbook, and then open it again.

If a security warning appears, click Enable Content, to allow the macros to run.

Then, if the workbook doesn’t already have a sheet for the current month, a new sheet will be automatically added, named with the year and month.

Get the Sample File

To see how the code works, and add month sheets automatically, get my sample file from my Contextures website.

Go to the Excel Worksheet Macros page, and get the sample file from the Download section.

___________________________