Home > Structure > Workbook > Add Worksheets Create Excel Workbooks Worksheets Automatically Create worksheets automatically when file opens at start of month - watch the video below, to see the steps. In another example, click a worksheet button to create monthly workbooks and worksheets for entire year.

1) Create Worksheets Automatically

To make the most of Excel data analysis features, like pivot table and filters, it's best to have your data in one main table. However, sometimes you need to work with your data in a less than ideal setup, such as separate worksheets for each month, or in a different file for each month, with daily worksheet.

The macros on this page will help you set those up quickly, instead of manually creating the worksheets and workbooks.

• Automatically add new worksheet at start of month - 2 macros
  • a) Blank sheet - tab shows month number and name
  • b) Copy of Master month sheet - tab shows year / month
• Click button to create monthly workbooks, with daily sheets, for upcoming year

At the end of the page, you can download the sample Excel files with the macro VBA code that you need for these repetitive tasks.

2) Add Blank Sheet When Excel File Opens

In this video, see how to auto insert a new worksheet with the month name, when an Excel workbook opens at the start of the month.

There are written steps below the video, and you can download the sample file at the end of the page.

Macro to Add Sheet When Workbook Opens

Someone asked me how to automatically insert a new month sheet, when they opened their Excel file at the betinning of the month. So, I created this macro and sample file, and the video that shows how the macro works automatically.

Planning the Macro

Before writing an Excel macro, make a plan for what the macro needs to do.

-- 1) Overall Goal

-- 2) Required Steps

The plans for the worksheet macro are shown below

1) Overall Goal

For this macro, the goal is to add a new, blank worksheet with the month name, when the Excel file opens. This should only happen at the start of the month, if the current month's sheet doesn’t exist already.

2) Required Steps

Here are the basic steps that this macro needs to do:

• figure out what the current month is
• check for a sheet for that month
• add that month sheet, if it doesn't exist already

In the next section, you'll see how to do those steps with Excel VBA code in a macro.

Step 1: Get Current Month

First, to get the month name in Excel VBA code, you can combine two VBA functions:

• Date - returns the current date
• Format - formats a number or date in a specific format

In this example, the worksheet names will have this format: yyyy_mm

• yyyy: 4-digit year number
• _: underscore
• mm: two-digit month number

Here is the VBA code to return the current date, as a text string, in yyyy_mm format:

Format(Date, "yyyy_mm")

Other Date Formats

Instead of using the yyyy_mm date format, you could use one of the following formats in the VBA code, if you prefer.

NOTE: If there are multiple years of data in the workbook, be sure to include the year number in the date string, e.g. yyyy_mmm

• mmmm - full month name, e.g. January
• mmm - 3-digit month name, e.g. Jan
• mm - 2-digit month number, e.g. 01
• mm_mmm - month number and name, e.g. 00_Jan

Format(Date, "mm_mmm")

Variable for Date Text String

In the macro, there is a variable named strName. It stores the results from the Format and Date formula, as a text string.

strName = Format(Date, "yyyy_mm")

Step 2: Check for Existing Sheet

Next, the Excel VBA code will check for an existing sheet with the current month name, in the format that your macro uses. For this step, the code uses the strName variable that stores the date text string.

First, the code tries to get the name of the sheet named with the date text string (strName)

Sheets(strName).Name

Next, the code uses the VBA Len function to check the length of that sheet name, and see if it is greater than zero characters - True or False

Len(Sheets(strName).Name) > 0

In the macro, there is a variable named bCheck. It stores the results from the length test formula, as a Boolean value (true or false)

bCheck = Len(Sheets(strName).Name) > 0

True or False

If the workbook already has a sheet with the current month's name:

• That sheet's name will have at least one character (greater than 0)
• bCheck = True

If the workbook does NOT a sheet with the current month's name:

• That sheet does not exist, so there isn't a sheet name to check for length
• The nonexistent name does not have any characters (NOT greater than 0)
• bCheck = False

Step 3: Add Sheet if Needed

The final step in the macro is to add a new worksheet, if needed, using the date text string

• This section of the macro only runs if the bCheck variable is False (sheet not in workbook).
• First, the code adds a new worksheet in the active workbook, to the left of the first sheet that's currently in the workbook.
• Then, the macro names that sheet, using the formatted date string.

If bCheck = False Then
    Set ws = Worksheets.Add(Before:=Sheets(1))
    ws.Name = strName
End If

Full Macro to Add Month Worksheet

Here is the completed macro code to add a new worksheet, if needed, using a formatted date string as its name. This macro is stored on a regular code module in the workbook

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

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

If bCheck = False Then
    Set ws = Worksheets.Add(Before:=Sheets(1))
    ws.Name = strName
End If

End Sub

3) Run Macro When File Opens

To make the AddMonthWkst macro run automatically, when the workbook opens, you’ll create a Workbook_Open event. Instead of storing this event code in a regular code module, you'll store it in the Workbook module.

To add this code to your own workbook:

• Open the Visual Basic Editor
• In the Project Explorer list, find your workbook
• Right-click on the ThisWorkbook module, in the Microsoft Excel Objects folder.
• In the pop-up menu, click on View Code
• Then, paste the code below into the Code Module window that appears at the right side of the screen

Here is the event code to copy and paste:

Private Sub Workbook_Open()
'add new sheet each month
    AddMonthWkst
End Sub

4) Add Template Month Sheet Automatically

Set up a momthly template sheet in your workbook, and automatically create a copy of that master sheet each month, named for the month and year, in yyyy_mm format.

Note: To add multiple month sheets, go to the next section: Add Multiple Month Sheets

This macro runs automatically when the workbook opens, and macros are enabled. There are two bits of code in the sample workbook. There is a sample file with these macros in the Download section below.

Watch this video to see the steps, and the code and instructions are below the video.

Master Sheet

This is the monthly template master sheet, from the sample file, and it will be copied to create a new sheet each month

ThisWorkbook Module

In the ThisWorkbook code module, there is a Workbook_Open event, and it runs the AddMonthWkst macro.

Private Sub Workbook_Open()
AddMonthWkst
End Sub

modSheet Module

In the modSheets code module, there is macro named AddMonthWkst. This macro:

• 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

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

5) Add Multiple Month Sheets

Set up a Master sheet, named Wkst_Master, in your workbook, and use this macro to automatically create copies of that sheet, for multiple months. Get the sample file in the Download section below.

To customize the sheets, go to the sheet named Wkst_Admin, and enter the year, month, sheet name and sheet title information. For the sheet names and sheet titles:

• Use yy or yyyy to include year number
• Use xx, xxx, or xxxx to include month name or number

The macro will replace those characters when creating the sheets.

modSheet Module

In the modSheets code module, there is macro named AddMultiMonthWksts. This macro:

• calculates the selected year and month names
• uses those in sheet name, and title (if entered)
• checks for a sheet with that name
• if not found, it creates a new sheet, based on Wkst_Master

NOTE: If you change the Admin sheet or Master sheet names, also change those names in the code.

Sub AddMultiMonthWksts()
Dim wsA As Worksheet
Dim wsM As Worksheet
Dim wsMth As Worksheet
Dim bCheck As Boolean
Dim wsYr As Long
Dim strCell As String
Dim strTitle As String
Dim strTabs As String
Dim wsTitle As String
Dim wsTab As String
Dim wsStart As Long
Dim wsEnd As Long
Dim wsSht As Long
Dim shDate As Date
Dim strMthT As String
Dim strMthTb As String
Dim strYtT As String
Dim strYrTb As String
Dim strMsg As String

On Error Resume Next
Set wsA = Sheets("Wkst_Admin")
Set wsM = Sheets("Wkst_Master")
strMsg = "Could not add all sheets"

On Error Resume Next
With wsA
  wsYr = .Range("wsYr").Value
    If wsYr = 0 Then
      strMsg = "Please enter year number" _
        & vbCrLf _
        & "and try again."
      GoTo errHandler
    End If
  strCell = .Range("wsCell").Value
  strTitle = .Range("wsTitle").Value
  strTabs = .Range("wsTabs").Value
  wsStart = .Range("wsStart").Value
  wsEnd = .Range("wsEnd").Value
    If wsStart = 0 Or wsEnd = 0 Then
      strMsg = "Please enter month start " _
        & "and end numbers" _
        & vbCrLf _
        & "and try again."
      GoTo errHandler
    End If
End With
On Error GoTo errHandler

'add sheets before master sheet
For wsSht = wsStart To wsEnd
  strMsg = "Could not get date for" _
      & vbCrLf _
        & "Year " & wsYr _
          & " and Month " & wsSht
  shDate = DateSerial(wsYr, wsSht, 1)
  
  strMsg = "Could not get sheet name for" _
      & vbCrLf _
        & "Year " & wsYr _
          & " and Month " & wsSht
  wsTab = Range("wsTabs").Value
  wsTab = Replace(wsTab, "xxxx", _
      Format(shDate, "mmmm"))
  wsTab = Replace(wsTab, "xxx", _
      Format(shDate, "mmm"))
  wsTab = Replace(wsTab, "xx", _
      Format(shDate, "mmm"))
  wsTab = Replace(wsTab, "yyyy", _
      Format(shDate, "yyyy"))
  wsTab = Replace(wsTab, "yy", _
      Format(shDate, "yy"))
On Error Resume Next
  bCheck = Len(Sheets(wsTab).Name) > 0
On Error GoTo errHandler
  If bCheck = False Then
  'add new sheet after Instructions
  strMsg = "Could not get add sheet " & wsTab
      wsM.Copy Before:=wsM
      With ActiveSheet
        .Name = wsTab
        If strCell <> "" Then
          wsTitle = strTitle
          wsTitle = Replace(wsTitle, "xxxx", _
            Format(shDate, "mmmm"))
          wsTitle = Replace(wsTitle, "xxx", _
            Format(shDate, "mmm"))
          wsTitle = Replace(wsTitle, "xx", _
            Format(shDate, "mm"))
          wsTitle = Replace(wsTitle, "yyyy", _
            Format(shDate, "yyyy"))
          wsTitle = Replace(wsTitle, "yy", _
            Format(shDate, "yy"))
          .Range(strCell).Value = wsTitle
        End If
      End With
  End If

Next wsSht

exitHandler:
  Set wsA = Nothing
  Set wsM = Nothing
  Set wsMth = Nothing
  Exit Sub

errHandler:
  MsgBox strMsg
  Resume exitHandler
	End Sub

6) Create Workbooks for Year

In the download section below, there is another sample Excel file that automatically creates worksheets, and it creates workbooks too, created by Roger Govier.

You can use this macro at the end of the year, when you want to set up your files for the upcoming year.

• This macro will create a series of 12 workbooks in the same folder as this workbook is stored.
  • Tip: Store the macro workbook in a new folder, named for the upcoming Year
• Each new workbook will be named with 3-character month name, and year e.g. Jan 2023 through Dec 2023.
• After you click the Macro button, you’ll be prompted to enter the year number at the beginning of the macro.

• Another message asks if you want the month's day to be in ordinal format on the sheet tabs, like 1st, 2nd, etc.
  • Click Yes if you want Ordinals
  • Click No for normal number format

Run the Macro

After you download the Create Workbook macro file, go to Windows Explorer, find the downloaded file, and follow these steps to unblock it:

• Right-click on the file, and click Properties
• If there is a Security message at the bottom, click the Unblock setting, to remove the check mark
• Click OK to close the Properties window.

Next, open the sample file, and go to the Information worksheet. There's a notes section there, so start by reading that.

• After you read the macro notes, click the worksheet button, to start the macro.

Remember -- the monthly workbooks will all be created in the same folder where the Create Workbooks macro file is stores.

Worksheet for Each Day in Month

Within each workbook, the macro will create a worksheet for each day of the month.

• There’s an option to display the numbers as ordinals, so the sheet names would be Jan 1st, Jan 2nd and so on.

Two Versions of Create Workbooks Macro

There are two versions of the Create Workbooks Macro workbook:

1. xls Format: Roger's original file creates workbooks in xls format - compatible with any version of Excel
2. xlsx Format: My updated version creates workbooks in xlsx format - for Excel 2007 or later

Both versions are in the Download section below.

7) Get the Excel Files ✅

• Add Blank Sheet When File Opens -- Download the file that automatically adds a new blank worksheet with the month name, when the Excel file opens at the start of the month.
• Month Sheets From Master: Download the sample workbook to automatically add month sheets, based on the Master sheet. The file is zipped, and is in xlsm format.
• Multiple Month Sheets From Master: Download the sample workbook to add multiple month sheets, based on the Master sheet. The file is zipped, and is in xlsm format.
• Monthly Workbook Creator (xls) -- Click a button, and the code in this file creates a workbook (xls format) for each month of the year, with a sheet for each day. Excel template from Roger Govier. CreateMthlyWkbks.zip
• Monthly Workbook Creator (xlsx) -- Click a button, and macro creates a workbook (xlsx format) for each month of the year, with a sheet for each day. Updated version of original template. excelcreateyrmthworkbks.zip

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Workbook Tips

Workbooks, List and Close

Worksheet Macros

Macros - Get Started

Macros - Save Sheets As PDF