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.

change worksheet month sheets from master

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.

monthsheetsmaster02

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.

monthsheetsmaster03

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.

monthsheetsmaster04

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.

monthsheetsmaster06

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.

monthsheetsmaster05

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.

___________________________

Add Month Sheets Automatically

Add Month Sheets Automatically in Excel

___________________________

Original source: https://contexturesblog.com/archives/2019/05/09/add-month-sheets-automatically-in-excel/

Tags: , ,

Leave a Reply

Close Menu